Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock Notifications in SQL Server 2005


Deadlock Notifications in SQL Server 2005

Author
Message
GarnetR
GarnetR
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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'.
Patrick_LeBlanc
Patrick_LeBlanc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 324
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



digivince
digivince
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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/
DBA-640728
DBA-640728
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1993
hi is there a way to add the deadlock graph information from the log into your email notification without having to create an event?
Patrick_LeBlanc
Patrick_LeBlanc
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 324
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.



sqldba-294117
sqldba-294117
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search