Deadlock Notifications in SQL Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Patrick LeBlanc » Deadlock Notifications in SQL Server 2005

16 posts, Page 2 of 2

««1 2

Deadlock Notifications in SQL Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

GarnetR

Posted Thursday, February 28, 2008 12:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2008 9:46 PM
Points: 1, Visits: 1

When I cut and pasted the first procedure into a new query window and attempted to execute, I got a whole series of syntax errors:

Msg 102, Level 15, State 1, Procedure usp_DeadlockNotification, Line 5
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 28
Must declare the scalar variable "@Filter".
Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 31
Must declare the scalar variable "@Minutes".
Msg 156, Level 15, State 1, Procedure usp_DeadlockNotification, Line 38
Incorrect syntax near the keyword 'ELSE'.

Post #461928

Patrick_LeBlanc

Posted Friday, February 29, 2008 7:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:22 AM
Points: 226, Visits: 278

Try this one:

IF OBJECT_ID( 'dbo.usp_DeadlockNotification') IS NOT NULL
DROP PROC dbo.usp_DeadlockNotification
GO
CREATE PROC dbo.usp_DeadlockNotification
@FilterBIT int = 0,
@Minutes INT = 30
AS
DECLARE @ErrorLog TABLE
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)

DECLARE @Count INT,
@StartDate DATETIME,
@EndDate DATETIME

SET @Count = 0

SET NOCOUNT ON

-- Step I: Import Errorlog
INSERT INTO @Errorlog
EXEC xp_readerrorlog

---- Step II: How to search Errorlog
IF (@FilterBIT <> 0)
BEGIN
SELECT @EndDate = GETDATE()
SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)

SELECT @Count = COUNT(*)
FROM @Errorlog
WHERE LogDate BETWEEN @StartDate AND @EndDate
AND LogInfo LIKE '%Deadlock%'
END
ELSE
BEGIN
SELECT @Count = COUNT(*)
FROM @Errorlog
WHERE LogInfo LIKE '%Deadlock%'
END

---- Step III: Send Email
IF (@Count > 0)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLTestCluster',
@recipients = 'pleblanc@lamar.com',
@subject = 'Deadlocks',
@body = 'Please check errorlog for Deadlocks'
END

Post #462351

digivince

Posted Thursday, March 05, 2009 1:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 07, 2010 3:05 PM
Points: 6, Visits: 38

Such complexity to handle something built-in and simple to configure? I concur... use alerts instead!

USE [msdb]
GO
--create "Operator" with customer's email
EXEC msdb.dbo.sp_add_operator @name=N'AppSupport',
@enabled=1,
@email_address=N'customer@emailthem.com',
@category_name=N'[Uncategorized]'
GO
--create deadlock alert
EXEC msdb.dbo.sp_add_alert @name=N'DeadLocks',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=5,
@include_event_description_in=1,
@notification_message=N'SQL Server deadlock alert.',
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/

Post #669594

DBA-640728

Posted Thursday, August 27, 2009 2:58 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

hi is there a way to add the deadlock graph information from the log into your email notification without having to create an event?

Post #778733

Patrick_LeBlanc

Posted Monday, August 31, 2009 9:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:22 AM
Points: 226, Visits: 278

Not without searching extensively through the error log. I would suggest using the Profiler or as you stated in your posts events, specifically extended events if you are running SQL 2008. If you would like to talk further about this please feel free to email me a pleblanc @tsqlscripts and I am sure we can figure something out.

Post #779987

sqldba-294117

Posted Thursday, September 29, 2011 4:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:52 PM
Points: 214, Visits: 336

I have made minor change to that code as shown below & shorten with few lines.

Create this store proc and schedule in SQL Agent Job for 10mins. it will only email if there is deadlock.

sp_send_dbmail as limitation that local variable cannot be pass within @query, but global variable helps & dropping at the end.

CREATE PROC [dbo].[usp_DeadlockNotification]

AS

CREATE TABLE ##ErrorLog
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(15),
text VARCHAR(15)
)

DECLARE @StartDate DATETIME,@EndDate DATETIME

DECLARE @Minutes INT

SET @Minutes = 10

SELECT @EndDate = GETDATE()
SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)

INSERT INTO ##Errorlog
EXEC master.dbo.xp_readerrorlog 0, 1,'deadlock-list',NULL, @StartDate, @EndDate, N'desc'

Declare @text varchar(15)
SET @text = (select 1 from ##Errorlog where text = 'deadlock-list' )

print @text

select * from ##Errorlog

IF (@text = 1)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = SQL DBA',
@recipients = 'SQLDBA@abc.com',
@subject = 'Deadlock Encounter',
@attach_query_result_as_file = 1,
@query = 'BEGIN

select * from ##Errorlog

END'
ELSE
SELECT 'No deadlock found!'

DROP Table ##Errorlog

Post #1183478

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions