Find deadlock in SQL Server

Introduction

A common issue with SQL Server is deadlocks. When a process stuck in deadlock, it prevents other process to execute for an infinite time or until forcefully that is killed. Here in this article we will see how to find out the deadlock process, see the details of the process and how to kill it using simple SQL query.

What is SQL Server DeadLock?

A deadlock occurs when two or more processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it, because the other process is preventing it. The results in a standoff where neither process can proceed.

Getting Started

A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward. When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can move forward.

By default when this occurs, your application may see or handle the error, but there is nothing that is captured in the SQL Server Error Log or the Windows Event Log to let you know this occurred. The error message that SQL Server sends back to the client is like the following:

 Msg 1205, Level 13, State 51, Line 3  
 Transaction (Process ID xx) was deadlocked on {xxx} resources with another process   
 and has been chosen as the deadlock victim. Rerun the transaction.  

Find SQL Server DeadLock

Below are the SQL stuffs which helps to find out the deadlock in SQL, view the view the deadlock process and kill the SQL server deadlock.

SP_WH2

This store procedure provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

When you execute the store procedure, the result set of sp_who2 will give a list of processes which contains a column named BlkBy, this represents the SPID, that is currently stopping the SPID in the row.

If you see a number there in the BlkBy column then that is the process id that is locked, now you can find out more about what it was doing by running and inputbuffer command for the moment we’ll pretend that my locked process is 68 then I can see what it was doing by typing.

sp_who2 example
 SP_WHO2 ACTIVE  

To list out active processes write above SQL query on SQL Query writter and press F5 button. the results will be appearn in reasult window.

dbcc inputbuffer

This provides information about the last statement executed by SPID. This helps us to find out the SQL query which causes the deadlock in SQL.

dbcc inputbuffer example
 dbcc inputbuffer(68)  

Kill Command

Ends a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has much work to undo, the KILL statement may take some time to complete. The process takes longer to complete particularly when the process involves rolling back a long transaction.

KILL ends a normal connection, which internally stops the transactions that are associated with the specified session ID. At times, Microsoft Distributed Transaction Coordinator (MS DTC) might be in use. If MS DTC is in use, you can also use the statement to end orphaned and in-doubt distributed transactions.

Kill command example
 KILL 68  

SQL Server Head Blocker

Blocking in SQL servers happens when a connection to SQL server blocks one or more query, and another connection to SQL server requires a conflicting lock type on query, or query locked by the primary connection which leads to the another connection waiting until the primary connection releases its locks. One connection can block another connection, A few seconds of blocking is normal and avoidable. But an excessive amount of blocking can cause connections (applications or user) to attend extensive periods of your time, no matter whether or not they are from identical application or separate applications on different client computers.

The sp_who and sp_who2 commands are older commands to show all current sessions. The DMV sys.dm_exec_requests returns more data in a result set that is easier to query to check blocking in sql server. Run this sample query to find blocking sessions in sql server.

 SELECT  
   session_id,  
   start_time,   
   [status],  
   command,  
   blocking_session_id,  
   wait_type,  
   wait_time,  
   open_transaction_count,  
   transaction_id,  
   total_elapsed_time,  
   Definition = CAST(text AS VARCHAR(MAX))  
 FROM  
   SYS.DM_EXEC_REQUESTS  
   CROSS APPLY sys.dm_exec_sql_text(sql_handle)   

Refer to the sys.dm_exec_requests and reference the blocking_session_id column. When blocking_session_id = 0, a session isn't being blocked. While sys.dm_exec_requests lists only requests currently executing, any connection (active or not) will be listed in sys.dm_exec_sessions. Execute the below query to know the session details of blockings. Keep in mind to be returned by sys.dm_exec_requests, the query must be actively executing with SQL Server.

  SELECT * FROM SYS.dm_exec_sessions WHERE session_id IN   
  (  
      SELECT session_id  
      FROM SYS.DM_EXEC_REQUESTS  
      CROSS APPLY sys.dm_exec_sql_text(sql_handle)   
      WHERE blocking_session_id != 0  
 )  

Summary

In the above of this blog, we learn how to detects and kills deadlock in SQL server. I hope you have enjoyed it a lot.

Thanks

Kailash Chandra Behera

An IT Professional with 12 years experience in development life cycle in windows, service and Web based application using Microsoft.Net technologies. Proven record of developing all phases of projects in Microsoft.Net technology from initiation to closure aligning with the company's Business objectives to drive process improvements, competitive advantage and bottom-line gains. -> Good exposure of independently working and developing multiple projects ->Committed to efficient and effective development of projects in a fast-paced and deadline driver environment. Skill :- Develop and design projects in various technologies of Microsoft Technology. Total IT Experience- 13+

Previous Post Next Post

نموذج الاتصال