SQL Script to Show Exclusive Locks on Tables

In this post I’m sharing a SQL script that will show exclusive locks for all tables in a database, with added info to help you understand what we’re looking at.

Exclusive locks are a natural thing in SQL Server which ensures data integrity during updates or deletions by preventing other transactions from accessing the locked resource. However, prolonged exclusive locks can cause performance issues, especially when planning maintenance tasks or deploying schema changes.

Script to Show Exclusive Locks

This script retrieves information about exclusive locks on tables in a specified database, including session details, lock types, and object names:

The output shows the DatabaseName and TableName with the exclusive lock. The SessionID identifies the session holding the lock, while HostName and LoginName tell you which machine and user are involved. This helps pinpoint the source of the lock.

This is a custom script I’ve made while writing this post. I’ll update if I find any faults or useful updates. The sys.dm_tran_locks MS Docs page is particularly useful for further information on understanding each columns meaning.

Operations Blocked and Allowed with Exclusive Locks

When a table has an exclusive lock, certain operations are blocked, while others may still be possible depending on the type and level of the lock applied.

SQL Server uses different lock types, like exclusive (X), shared (S), and intent (I), and lock levels (row, page, or table) to manage concurrency and ensure data integrity. For example, an exclusive lock (X) prevents other transactions from accessing or modifying the locked resource, but the lock’s granularity (row, page, or table) determines if other parts of the table can still be used.

What Can’t Be Done
Reading or Writing Data:
Other transactions cannot modify or acquire shared locks to read the locked data. Using the NOLOCK table hint allows reading data, but this can result in dirty reads (uncommitted data) or inconsistent results.
Schema Changes:
ALTER, DROP, or adding the table to replication is blocked due to schema-related locks.
Truncating the Table:
TRUNCATE is restricted due to conflicts with schema-level locks that prevent structural changes during a lock.

What Can Be Done
Identify Blocking Transactions:
Use system views like sys.dm_tran_locks or sp_who2 to locate the cause of the block.
Access Unlocked Parts:
If the lock is row- or page-specific, other parts of the table may still be accessible for reading or writing.

Managing Exclusive Locks During Changes

To minimize the impact of exclusive locks, it’s important to plan changes during off-peak hours and schedule maintenance windows when database activity is low. If you’re struggling to perform a DDL change on a table, consider arranging application downtime to allow the operation to complete without interference. For tasks like adding a table to replication, you can populate the subscriber first and then use the replication support-only option to avoid conflicts, ensuring smoother changes with minimal disruption.

These strategies should help you maintain performance and reduce the risk of lock contention during critical operations. As a follow-up, you could also be checking the currently running SPIDs on your SQL Server for more information on busy periods.