August 7, 2009 at 10:21 am
Hi,
We have sql server 2005.We got to know from users the application is very very slow and then I noticed many dead locks. After that every day dead locks occurring frequently.Here my problem is, I coming to know there were dead locks occurred, only when I go htrough the error log .
Now I want to have a script which checks for dead locks in the error log very 10 mins and as soon as it finds the dead locks, I want to get an email. Is there any script like this? How you guys get an alert when dead lock occurs?
August 7, 2009 at 11:01 am
Can't you just set up an Alert (using SQL Server Agent's Alert feature) to monitor the SQL Server: Locks - Number of Deadlocks/sec counter?
August 7, 2009 at 11:43 am
Or how about setting up a server side trace to capture deadlock graphs. That will give you a lot more information. Besides, what are you going to do about a deadlock at 3AM? The transactions have already completed their processes by the time the deadlock will be transmitted to you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2009 at 3:38 pm
Thanks
I want to know how experienced DBA's deal with Deadlocks.
When a dead lock occurs, how you get notified:
1.Only by examining the error log
2.Notification by email,by frequently running a script which monitors the dead locks in the error log and triggers an email if it finds any dead lock?
3.Use SQL Server agent Alerts
how you deal?
August 7, 2009 at 3:41 pm
Agent alerts is the easy way. Why have something polling the server when it's already built in.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply