SQL Server - How to automated Deadlocks finding in SQL server

Asked By prathap chowdary on 01-Feb-12 02:53 AM

Hi,

In my sql server 2008, i have enabled trace flags 1204,1205(server level means globally) for dead lock detecting. if deadlocks occured on server, we got dead lock details in sql server logs........

But i want, how can i get it to my mail.......that same information

Please anyone help out me.........

Thanks in advance

Reena Jain replied to prathap chowdary on 01-Feb-12 03:13 AM

Hi,

For this please note that you must have Database Mail configured,
SQL Server Agent must have the correct Alert System Configurations set to utilize this method and
Traceflag 1222 must be on (DBCC TRACEON(1222, -1).

ALTER PROC [dbo].[usp_DeadlockNotification]

AS

SET NOCOUNT ON

INSERT INTO DeadLocks([LogDate],[ProcessInfo],[Text]

) EXEC sp_readerrorlog 0, 1, 'deadlock'

;WITH cte

AS

(

SELECT *,ROW_NUMBER()OVER (PARTITION BY

LogDate ORDER BY LogDate )rn

FROM DeadLocks

) DELETE FROM cte WHERE rn>1

IF EXISTS (SELECT * FROM DeadLocks

WHERE DATEDIFF(HOUR,Logdate,GETDATE())<=1)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA',

@recipients = 'reenajain@eggheadcafe.com',

@subject = 'Deadlocks',

@body = 'Please check errorlog for Deadlocks'

END

prathap chowdary replied to Reena Jain on 01-Feb-12 03:24 AM

Hi,

I am already configured Database mail.........and how to configure SQL SERVER AGENT alert configuration.........
and please specify that above script runs under which database.........
And before run the above query........any needs to configure alert on sql server agent.

Please help out me..........

prathap chowdary replied to Reena Jain on 04-Feb-12 03:41 AM

hi Reena,

when i am trying the above query i got this error :

Msg 208, Level 16, State 6, Procedure usp_DeadlockNotification, Line 20

Invalid object name 'dbo.usp_DeadlockNotification'.

Please help me ......