August 13, 2007 at 2:50 pm
I have a database that within the last 2 weeks started having deadlocks, sometimes several an hour. The database has not changed significantly and there has been no change in the number of users connecting to the db. I ran a deadlock trace and the process that is winning the deadlock every time is listed as "system" user and the database is "no database context" (status is background). I cannot find anything useful on this type of user to indicate why it would be locking specific records in my database. I am not a full-time dba and would greatly appreciate any ideas on what might be going on.
Thanks in advance,
BK
August 13, 2007 at 3:13 pm
In order to find out more useful information about your deadlocks at the moment they occur, create 3 alerts for error numbers 1205 ( SPID # was deadlocked), 3928 (transaction is deadlocked while attempted to place a mark in the log) and 5231 (deadlock occured while trying to lock a object for checking).
Create a job which will execute sp_lock with saving results into some auxiliary table ( not a temporary table). Optionally, you can also query sysprocesses table or execute sp_who in the same manner. Call this job from Response tab from all these alerts. Also you can check to notify yourself via email so you can check that transaction right after it occured.
Hope this will provide you with some info.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply