Click here to Skip to main content
16,016,345 members
Articles / Programming Languages / SQL
Tip/Trick

sp_who2 to find dead locks in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.88/5 (8 votes)
8 Jan 2015CPOL 86.7K   12   6
Find deadlocks in SQL Server using sp_who2 (an undocumented and thus unsupported stored procedure)

Introduction

sp_who2 is a undocumented thus unsupported stroed procedure in SQL server, but widely used inststed of sp_who to list processes currently active in SQL Server. Both these procudures are designed to retrive same result set, however sp_who2 adds some extra columns which sp_who does not include. Furthermore, sp_who2 makes an effort to make the display to be as compact as possible for output in text mode.

sp_who2 usually helps me to track / kill deadlocks in my database.

Using the code

The result set of sp_who2 will contains a column named BlkBy, this represents the SPID that is currently stopping the SPID in the row.

Sometimes many rows will show SPID numbers in the BlkBy column.

This is because there is a chain of blockers. The way this occurs usually starts with one “lead blocker” blocking another process.=

C++
//
// sp_who2
//

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionGreat Information! But.. Pin
Tailslide9-Jan-15 9:19
Tailslide9-Jan-15 9:19 
Can you update your screen shot to show what the values for the head blocking process look like? I am thinking if I periodically poll for the head blocker and write to a file I can catch deadlocks that happen when I am away.
AnswerRe: Great Information! But.. Pin
Liju Sankar15-Jan-15 0:01
professionalLiju Sankar15-Jan-15 0:01 
GeneralMy vote of 3 Pin
Kyle Moyer9-Jan-15 8:33
Kyle Moyer9-Jan-15 8:33 
GeneralRe: My vote of 3 Pin
Liju Sankar15-Jan-15 0:02
professionalLiju Sankar15-Jan-15 0:02 
GeneralMy vote of 5 Pin
Carsten V2.09-Jan-15 6:45
Carsten V2.09-Jan-15 6:45 
AnswerRe: My vote of 5 Pin
Liju Sankar31-Jul-15 9:56
professionalLiju Sankar31-Jul-15 9:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.