Finding and Terminating SQL Server Blocking Processes – Dead Locks in MSQL
We can free or release or remove or delete or check all locks on the sql server 2008,2012 and 2014 objects like table etc by killing the process id which is keeping the locks on the objects like tables and views
Solution
In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process. There are many different ways in SQL Server to identify a block and blocking process that are listed as follow:
- sp_who2 System Stored Procedure
- Sys.dm_os_waiting_tasks
- SQL Server Management Studio Activity Monitor
- SQL Server Management Studio Reports
- SQL Server Profiler Locks Event Category
- DMVs
- sys.dm_exec_requests
- sys.dm_tran_locks
- sys.dm_os_waiting_tasks
Summary of Fasted Steps to find and Kill Dead Locks in MSQL:
Once the new SQL Server query window opens, type the following TSQL statements in the window and execute them:
Find process:
USE Master
GO
EXEC sp_who2
GO
A list of processes will be displayed, and any processes which are currently in a blocked state will display the SPID of the processes blocking them in the ‘BlkBy’ column.
USE Master
GO
EXEC sp_who2 — display a list of all SPIDs and their status in the results windows. Use this list of processes to confirm the SPID you wish to kill.
GO
kill process:
KILL <SPID>
GO
EXEC sp_who2
GO
Blocking Query and Resolution
If faced with a condition where he was not able to delete data from a table. He already tried to TRUNCATE, DELETE and DROP on the table, but still no luck. Here is the script we used to identify the blocking query:
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
OR
SELECT db.name DBName,
tl.request_session_id as ReqSessionId, esr.host_name as ReqHost, esr.program_name as ReqProgram, esr.client_interface_name as ReqClient, esr.login_name as ReqLogin,
wt.blocking_session_id as BlkSessionId, esb.host_name as BlkHost, esb.program_name as BlkProgram, esb.client_interface_name as BlkClient, esb.login_name as BlkLogin,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
from sys.dm_tran_locks AS tl
inner join sys.databases db ON db.database_id = tl.resource_database_id
inner join sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
inner join sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
inner join sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
inner join sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
join sys.dm_exec_sessions esr on esr.session_Id = tl.request_session_Id
join sys.dm_exec_sessions esb on esb.session_Id = wt.blocking_session_id
cross apply sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
cross apply sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
Go
One thing that you will most certainly run into at one point or another when working with Microsoft SQL Server, or any other Relational Database Management System (RDBMS), is blocked processes caused by locks on database objects. But what are database locks, and why can they sometimes cause one process to block another?
Recent Comments