May 9, 2006 at 12:51 pm
Anyone know of a tool (or have a script) which will allow me to quickly identify SQL 2000 DEADLOCK information using TRACE files as input?
I have TRACE started via DBCC TRACEON(3605, 1204) ----
I found one tool from http://www.cleardata.biz/cleartrace/default.aspx which allows me to very quickly import and report on TRACE data (using multiple trace files as input) but I don't see DEADLOCK info w/ this..
I'd like to see the CAUSING resource, VICTIM resource, RESOURCE contended, etc..
thx in advance
May 9, 2006 at 1:28 pm
Those traceflags will have SQLserver log deadlockinfo to the SQLServer ErrrorLog-files.
look at yourdrive:\MSSQL\LOG\ERRORLOG
or use EM / Management / Sql server logs
search for Deadlock
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 10, 2006 at 6:05 am
Those trace flags writing deadlock info to the errorlog is the only thing I have used as well.
The deadlock information is easy to pick out of the errorlog.. I know some people have a little trouble interpretting everything they see in there though. The MS site/book online has some examples that would be helpful for that though.
For example, when a deadlock is encountered it will write information about the "Owner" of the resource in question. A lot of times with the deadlock the owner will have an Exclusive lock on the resurce (shown by the Owner with Mode: X). You'll also see the Ownder SPID and input buffer which is helpful.
Similarly you'll see who is requesting that same resource ("Requested By: SPID and intended lock type") and if there was a deadlock victim chosen then you'll see that too ("Victim.. SPID, etc..")
As far as the resource in contention you have to do a little more digging, but you'll see a KEY notation that is made up of "database id:object id:index id" - those values you can query (sysdatabases and sysobjects if I remember right) to get the info on resources.
Also- I've never personally used it for this, but I know SQL profiler has some deadlock events too (Deadlock and Deadlock Chain) that could be helpful.
Good luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply