Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Performance

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-09 : 09:24:08

Deadlocks are occuring during on server.
I need a script to setup an email alert when the deadlock occured.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-09 : 09:40:31

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Once that alert is created, you'll have to right-click it under SQL Agent and add the option to Notify Operators.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-09 : 10:09:18

Thanks Robvolk.

I am new deadlocks stuff..
Please let me know how to identity the which database and query is causing the deadlock..do i need to enable the dbcc trace also.

Please suggest me if extended events can be used for alerts..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-09 : 10:16:55

I have enable the operators as mentioned.. i need to enable the execute the job option in the alert.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-09 : 10:29:48

If you're new to deadlocking I recommend reading/viewing Jonathan Kehayias' material on them:

http://www.sqlshare.com/Search.aspx?terms=deadlock
http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx
http://www.sqlservercentral.com/articles/deadlock/65658/

If you need details on which database, statement, user, etc. caused the deadlock then you'll probably need to used Extended Events, another area Jonathan is expert in. If you can't find something on his site that already does this you can try his current blog: http://www.sqlskills.com/blogs/jonathan/ , there's a contact link at the top if you need to email or tweet him.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-09 : 12:42:23

Thanks for the below links and i will go through it.
Meanwhile
Please correct me the below scritp should alert when the deadlocks occured..

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-09 : 22:11:22

That script creates an alert on SQL Server, but it doesn't add notifications. You have to add those manually through Management Studio, as I mentioned earlier.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-11 : 11:39:33

Thanks .

I have created the alerts and setup notification.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-12 : 13:02:44

I put the alert and setup the notitication but there was deadlock happend but didn't email the alert about the deadlock..

Please suggest..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 13:40:24

Did you configure Database Mail on that server? Make sure to set up a default mail profile as well. Once that's done right-click on SQL Server Agent in Object Explorer, choose Properties, and go to the Alert System panel to enable Database Mail.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-12 : 13:47:56

Database mail was already configured..but there is any alert already exist with same jobid could that be a problem but when i tried to see property of the alert then i got the error..

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

Failed to create/initialize Agent Alert dialog. (SqlManagerUI)

------------------------------

Cannot create/initialize Response page. (SqlManagerUI)

------------------------------

An item with the same key has already been added. (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------

Please help to resolve the issue..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 13:52:35

That error sounds like a possibly corrupted Management Studio install. Have you tried it from another computer and get the same error?

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-12 : 14:01:13

I don't have other server to test..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-12 : 14:02:18

Can you suggegts any soultion since it is critical for me to resolve it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 14:16:56

When you say it's "critical to resolve it" do you mean resolving the deadlocks, or the alert that the deadlock occurred?

There are other ways to log deadlock events. They can be captured in Profiler/Tracing, in the SQL Error log using trace flags 1204 and 1222, or through Extended Events as I mentioned earlier. And all of these methods provide more information about fixing the actual deadlock condition than the alert will.

Mladen has some articles on them too:

http://weblogs.sqlteam.com/mladenp/archive/2008/05/21/SQL-Server-2005-Immediate-Deadlock-notifications.aspx
http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

And there's a ton of information on attacking and preventing deadlocks:

http://msdn.microsoft.com/en-us/library/ms188246.aspx
http://support.microsoft.com/kb/832524
http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/

And get a test server. It can be your desktop computer with Developer Edition on it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-12 : 14:48:11

When we know deadlocks are happening on a system, we enable trace flag 1222. And disable it after we have resolved the deadlocks or are tired of them clogging up the error log with no resolution of the deadlocks.

What do we do with the deadlock output? Well we work with Microsoft on them. Resolving deadlocks aren't exactly easy.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-12 : 18:48:57

Deadlocks are happening during midnight and i want trying to see if i can set up deadlocks alerts.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-12 : 18:55:46

If you know when they are happening, then why bother with an alert?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-12 : 19:14:16

If you're on SQL 2008, don't waste time with alerts (which tell you nothing other than that a deadlock occurred) or even with profiler or traceflags. Query the system_health extended events session (enabled and running by default) that automatically captures deadlock info.

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-13 : 01:08:36

Thanks

Could please provide me the steps to enable the extended events

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-13 : 05:38:37

quote:
Query the system_health extended events session (enabled and running by default) that automatically captures deadlock info.

Hence no enabling needs doing.

--
Gail Shaw
SQL Server MVP

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources