July 12, 2007 at 9:27 am
I have a job that runs some DBCC commands and shrinks several user databases after hours. It quit with a deadlock condition last night. I can see in the event log how far the job got by the backup steps it recorded there, but is there any way to tell what it was deadlocked with so we can avoid this in the future?
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
July 12, 2007 at 11:40 am
I think its a bit late to find out what processes contributed to your deadlock situation, but you can enable trace flags 1204 and 3604 so that you can capture the deadlock details if this continues to happen.
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
July 12, 2007 at 11:54 am
Thanks John. These deadlocks happen during a maintenance job that runs once a week. If I read the article correctly, once I enable the trace flags, they will only write the log file if a deadlock happens, right?
July 12, 2007 at 12:01 pm
Yes. If SQL Server encounters a deadlock, the detailed info. will get written to the log file.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply