How to trace deadlock
-
How can I trace a deadlock without running sqlprofiler. The thing is, when I run sql profiler, the server will become very slow. I suspect a process is causing a deadlock, but I don't know how to trace it.
-
The easiest way probably is to run the sp_who2 in QA and see who is blocking. You can then dbbcc inputbuffer (spid) to see what they are running.
You can also set
dbcc tracestatus(-1)
dbcc traceon(1204,3605,-1)
dbcc tracestatus(-1)
Trace flags.
Good luck...
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
This has to be run when we suspect there is a deadlock (real-time). How about if we want to know for certain period of time, like yesterdays activities is there is any deadlock?
Thx
-
Set the the traceflags -T1204 and -T3605. This will write the basic deadlock info out to the SQL Server error log. Profiler will still be more useful in tracking down the specific causes of the deadlocks, but the information recorded in the error log will get you going.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
I posted a script that I used to debug an application for deadlocking. This script is currently waiting for approval, so when it is available you can get it here:
http://qa.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=1535
The script is commented for areas that need customization. I would only have the Job that this script should be run from active long enough to catch the information and correct the application, as it is designed to KILL the blocking process.
Be aware that this can affect long running queries as well, so the trick is to tweak the values for how long before a blocking process is considered a deadlock, my default is 120 seconds.
Andy
-
Sounds like a really good script. I'll check it out when it becomes available.
One point, blocking locks and deadlocks are two different things. Deadlocks are caused by two processes attempting to escelate locks on the same row/page/table/database at the same time. Usually through two different stored procedures or a stored proc with two different execution paths that cause the processes to establish shared locks and then both try to escelate to exclusive locks and one is chosen by the sql server engine as the deadlock victim and is immediately thrown out of the system. While these can be caused by long running processes that put out lots of locks, I've most frequently seen them in short quick processes that just weren't written quite right.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply