October 4, 2007 at 10:19 am
Just needed some help to try and track down the reason for a Deadlock
This is the error message im getting
Unspecified error
Number=1205 state= 40001 Source=Microsoft OLE DB provider for SQL server
Description=transaction(Process ID "------")was deadlocked on lock resources with another process and had been chosen as the deadlock victim. Rerun the trabsaction.
I ran a SQL trace, however, I’m stuck now as how to proceed, since it only shows the events, event class etc from when the trace has begun. Do I just leave the trace on and wait for a deadlock to occur and then analyze it? Or can I take a look at a deadlock which occurred prior to the trace.
I have also tried using DBCC traceon(3605,1204) but there are no errors which show up.
If this is not the correct way could you let me know what I can do…I’ll do more research and proceed. Just need some input and direction.
Regards
J.D.
October 4, 2007 at 10:26 am
The trace flags will put the trace data into the error log the next time a deadlock occurs. Check out this link, it will help:
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
October 4, 2007 at 10:54 am
Thanks, that does seem to be the best approach, however ive run the transaction a couple of times, but the errors do not show in the log files.
Do i need to run it every time SQL server starts up?
J.D.
October 4, 2007 at 1:56 pm
Setting a trace flag using DBCC TRACEON will leave the trace flag running until you run DBCC TRACEOFF or restart SQL Server. If you need the trace flags to run even after you restart SQL Server, you'll need to go into the startup parameters and add the -T 3604 and -T 1204 as startup parameters.
October 4, 2007 at 2:10 pm
Yeah exactly.. i have done that. However, when i take a look at the ERRORLOG files they do not show contain the deadlock report. I am able to view the deadlock using SQL enterprise manager. But that isnt giving me any details as to the process that is causing the deadlock. Only the nodees in volved in the deadlock and the SPID's ...
I need to see a parameter in like Input Buf: but nothing is getting displayed in the ERRORLOG file.
Like for example...for today the last update to the file was abt 5-6 hrs ago.
I'm pretty sure that this is the log file.
Let me know if you think of anything...thanks for taking the time though..i really appreciate it!
October 4, 2007 at 2:22 pm
What do you mean that it is not in the errorlog but you can see in in Enterprise Manager? Also, the input buffer (or part of it) is listed in the deadlock report. You can also use profiler to capture t-sql and configure it to report on deadlocks. You will not get a deadlock report like you do with the trace flags, but between the trace flags and a profiler trace, you should be able to drill down to the exact pieces of code that are deadlocking.
EDIT: What I'm saying here is to use the 2 tools together. The deadlock report from the trace flags can be cross-referenced to the profiler trace by SPID to show you what was going on during the deadlock. Hope this helps.
October 4, 2007 at 3:08 pm
What i mean is that for some reasons the error report of the deadlocks are not getting displayed..i look up the file from the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
However, when i look it up from the enterprise manager---->sql server logs..it does register the deadlock and nodes at least..but no mention of the process/or stored procedure.
I run a trace, would you recommend any parameters i use in th etrace for deadlock? secondly do i just leave the trace on..because if my undertsanding is correct..a trace would only display events after it has been started. MEaning i would have to wait for deadlock to occur and cannot analyze any prior transactions?
Thanks for your help..you seem to be the only one replying!but then again im new to this community....
October 4, 2007 at 4:28 pm
Either way, your stuck with not being able to go back in time to look at previous deadlocks. Have you been able to capture a deadlock in your log file since you've enabled the trace flag? If so, post the log file contents in this thread so I can have a peak.
As far as the Profiler trace. What I would look at is Locks:deadlock, Locks:Deadlock Chain, Performance: Execution Plan, Stored Procedures: SP:Starting, Stored Procedures: SP:Completed, Stored Procedures: SP:StmtStarting, Stored Procedures: SP:StmtCompleted, TSQL:StmtStarting, and TSQL:StmtComplted. I would recommend running the Profiler session on a machine other than your database server and make sure you have ample space. These trace files can get quite large. Only run it until you've captured the deadlock then make sure you get it shut off as it does add a bit of overhead to your system.
October 5, 2007 at 9:55 am
I'm leaving the trace on as for the whole day today..lets see what pops up...thnx again..!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply