August 26, 2003 at 8:20 am
Hi all,
I have a scheduled job (sp_updatestats) that executes once a week at midnight. However, it failed on the past Sunday with the following error message:
Executed as user: NT AUTHORITY\SYSTEM. Updating dbo.LU_NETDOMAIN [SQLSTATE 01000] (Message 0) Updating dbo.LU_CLIENT [SQLSTATE 01000] (Message 0) Updating dbo.LU_INSTANCE [SQLSTATE 01000] (Message 0) Updating dbo.LU_METRICCOUNTER [SQLSTATE 01000] (Message 0) Updating dbo.LU_METRIC [SQLSTATE 01000] (Message 0) Updating dbo.LU_USER [SQLSTATE 01000] (Message 0) Updating dbo.LU_PATH [SQLSTATE 01000] (Message 0) Updating dbo.LU_PROCESSNAME [SQLSTATE 01000] (Message 0) Updating dbo.LU_PROCESS [SQLSTATE 01000] (Message 0) Updating dbo.LU_SERVERNAME [SQLSTATE 01000] (Message 0) Updating dbo.LU_SERVER [SQLSTATE 01000] (Message 0) Updating dbo.SDB_EVENTLOG [SQLSTATE 01000] (Message 0) Updating dbo.LU_WINSTATION [SQLSTATE 01000] (Message 0) Updating dbo.SDB_METRICS [SQLSTATE 01000] (Message 0) Transaction (Process ID 62) 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.
I understand that the error message indicates that when the Update Statistics job executed, there was another process running simultaneously and it prevented my job from starting. The Update Statistics job was executed again the next morning and it completed fine.
I guess my question is, how can I find out what was being locked and by which process?
Thanks for any help in advance.
AW
Albert P. Wun
Consultant - Data Management - DBC
CGI Services to BCE
Markham, Ontario, Canada
August 26, 2003 at 9:05 am
Run dbcc traceon(1204, 3605, -1) to log dead lock information to your SQL Server errorlog.
August 27, 2003 at 8:20 am
I know what the trace flag #1204 is, but what does the trace flag 3605 represent ?
Thanks again.
AW
Albert P. Wun
Consultant - Data Management - DBC
CGI Services to BCE
Markham, Ontario, Canada
August 27, 2003 at 8:39 am
Send dead lock information to SQL Server errorlog.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply