Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. Source: https://docs.microsoft.com
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
WHEN -1 THEN DATALENGTH(st.text)
END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime,
ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name
FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss
ON qs.session_id = ss.session_id
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE Wait_Time > 0
ORDER BY Wait_Time DESC
1) Use an activity monitor to kill the process
Right-click on the SQL instance name -> Activity monitor -> Expand the process section -> Find the relevant process id and right-click to kill the process.
2) Run the kill command to kill the process
What is blocking Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency….
Please help !!!