Find and Remove Locks in Microsoft SQL Server |

Locks in MS SQL Server are a way of ensuring the integrity of data when changes are made by multiple users at the same time. MSSQL locks objects on tables when a transaction begins and releases the lock when the transaction ends. In this article, we will learn how to find and remove locks in MS SQL Server database.

It is possible to simulate a lock on a single table by using an incomplete transaction (that has not been completed by rollback or commit). For example, use the following SQL query:

USE tesdb1
DELETE TOP(1) FROM tblStudents

Before any changes are made, SQL Server will lock the table. Now try to open SQL Server Management Studio and execute a simple SQL select query:

SELECT * FROM tblStudents

query will freeze in “execute queryState until this time expires. This is because the SELECT query is trying to access data in a table that is locked by SQL Server.

Hang query in MS SQL due to table lock (BLOCK)

You can configure row-level or full-table-level locks in Microsoft SQL Server.

To get a list of all blocked queries in MSSQL Server, run the command

select cmd,* from sys.sysprocesses
where blocked > 0

You can also display a list of locks for a specific database:
SELECT * FROM master.dbo.sysprocesses
dbid = DB_ID('testdb12') and blocked <> 0
order by blocked

The Blocked column shows the process ID of the process that is blocking the resource. It also shows the wait time for this query (wait time in milliseconds). If necessary, this can be used to search for the earliest or latest locks.

Find blocked procedures in SQL Server

In some cases, the entire process tree may cause a lock. To find the source lock process, use the following query for SPID until you find the process blocked = 0Which is the initial process to hold the lock.

select * FROM
where 1=1
--and blocked <> 0
and spid = 59

The process SPID gives you the T-SQL code of the last SQL query executed by that process or transaction:


dbcc sql get query string

To end the process and release the lock, run the command:

KILL number_of_session

In my case, it would be:


kill procedure in mssql

If locks are occurring frequently and you want to identify the most resource-intensive queries, you can create a separate stored procedure:

@SPID int
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

Now, if you want to see the SQL query locking the table, you just need to specify its SPID:

Exec GetCurrentQueryCode 51

SQL Server Stored Procedure to find lock query

You can also get the query code from the sql_handle of the locking procedure. For example

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

select * from sys.dm_exec_sql_text

You can use Microsoft SQL Server Management Studio to find locks in MS SQL Server. You can use one of the following methods:

  • Right click on the server, open activity monitor expand further Procedures, You will see a list of requests waiting to release resources with a Suspended status. MSSQL: Activity Monitor Suspended Process
  • Select and navigate to a database reports , all blocked transactions, It also shows the list of locked queries and the SPID of the locking source. Microsoft SQL Server - All Blocked Transactions

Leave a Comment