March 26, 2009 at 10:01 am
Hi,
I am very confused about sql server locks.
I am monitoring Lock:Deadlock and Lock:Acquired Event Classes.
But i am confused about two things,
1- i am unable to find the objects which are involved in the locks, objectid and objectid2 are there in trace but how can i find the actual object names against these id's, i used object_name function but it is not giving me any name.
2- How would i know how long a dead lock has held? Becuase Duration of DeadLock event class shows the "Amount of time (in microseconds) between the time the lock request was issued and the time the deadlock occurred" while Duration of Acquired event class shows the "Amount of time (in microseconds) between the time the lock request was issued and the time the lock was acquired."
In simple words, i want to log dead locks that are held for more than 30 seconds, and then i want to know the objects involved in it.
HELPPPPPPPP........
Thanks,
Usman
March 26, 2009 at 10:23 am
have you got Trace flag 1222 enables on your server? If so, enable and it records the dead lock events in the error log.
check out bart's blog and get more understanding into the issue:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Any problems give us a shout.
March 26, 2009 at 12:18 pm
Thanks Krishna, very cool article, i just turned on the trace flag on my server, hopefully this will help.
But half of my confusion is still there, how would i know how long that deadlock was held? this is very important for us, cuz if the deadlock (or i should say blockage) is for few number of seconds (may be 10-20 at most 30 seconds) then we are fine with that, but if it is more than that i need a way to kill the process automatically (it does not matter if i lost a transaction by doing so).
By enabling this flag i will definetely be able to see the details of the query and objects, but in order to know that there was a dead lock i will have to check the error log file every now an then, so by the time i check it, may be all of our users will be blocked by the deadlock.....so i need an automated process that monitors deadlock or blockage, if its more than 30 seconds, atleast page us if not more.
Again thanks for you help, but i need more.....
-Usman
March 26, 2009 at 12:42 pm
You are welcome.
SQL server automatically chooses the dead lock victim and kills the process. You do not have to but yes it is sometimes critical to know on production databases.
Have you checked creating alerts for SQL Server locks objects? give a try on that and also check out this blog about sending notifications to your mail box just found it seems to be great thought It would help you:
March 26, 2009 at 1:03 pm
Another cool article,
BUT it's not enough to satisfy me, becuase of two reasons, first we are running a third party product and that application sends direct queries to database so no procedures to modify for notification, second, yes you are right sql server itself kills the victim in a dead lock but it does not kill the victim when it is blocked by some other query (remember its not deadlock its blockage),
so ideally i need a way to identify queries that are blocking other queries for more than 30 seconds, and the queries that are being blocked.
Sorry if i am confusing you, but still need help to figure this out.....
appreciate your cool articles though....
-Usman
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply