Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deadlock Notifications in SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, February 28, 2008 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, February 29, 2008 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:51 PM
Points: 229, Visits: 305
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
Posted Thursday, March 5, 2009 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 7, 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
Posted Thursday, August 27, 2009 2:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:26 AM
Points: 537, Visits: 1,923
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
Posted Monday, August 31, 2009 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:51 PM
Points: 229, Visits: 305
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
Posted Thursday, September 29, 2011 4:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:52 AM
Points: 214, Visits: 367
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 »

Add to briefcase ««12

Permissions Expand / Collapse