January 15, 2014 at 12:54 am
It's a really great post!!!!!!!:Wow: I even did not realze that it's possible to automate logging of the deadlock information using sp_readerrorlog and alerts. I thought that it is only possible using event notification (using queues with activation procedure). Do you know are there any additional information about deadlock due to the trace flag 1222 in comparison with the info that comes from the event notifications? What additional information about deadlock can be obtained using dbcc traceon (1222)?
January 15, 2014 at 9:46 am
Try a Service Broker based Event Notification. It's elegant and I find it works well.
January 15, 2014 at 10:08 am
I agree, Event Notifications work for Deadlocks and so much more. Here's my blog posting on how I created a "generic" event notification service for all of my SQL servers. It alerts on database creation and drops, login creations, role changes, etc.
http://www.the-fays.net/blog/?p=239
No trace flags, no agent jobs, no temp tables, no WMI calls. I'd recommend that anyone reading it, take strong consideration to the updates about using certificates rather than changing the TRUSTWORTHY setting on the database used.
May 30, 2014 at 8:21 am
Hi,
very good post..
But when deadlock occured i recived attachment in text file but sadly it didn't contain any data.
Is there something in select query? coz i cross check data inserted in a table propely but slect query didn't get any data degarding deadlock
August 8, 2014 at 6:52 am
adding (and logdate = GETDATE()) will send current date locks, otherwise it keeps sending old ones as well Thanks
November 2, 2016 at 4:53 am
Hi.. it is very useful script for monitoring deadlock event occur in OLTP databases.
I am facing same issues: But when deadlock occurred i received attachment in text file but sadly it didn't contain any data.
I enabled 1222 -1 trace flag in SQL 2012 instance.
Thanks
November 2, 2016 at 7:23 am
If you are in SQL 2012 I would use extended events. That said, the reason you are not getting information in the email is because the error log changed its wording so the line to change would be listed below.
Depending on the version of SQL you would need to choose between these options:
@query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''deadlock-list'''' order by Id DESC)'',
or
@query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
November 2, 2016 at 10:30 pm
Thanks Tony for your reply, I have tried and modified on existing script for file attachment as below, but its throwing errors
Please help.
declare @subject varchar(250)
select @subject = 'Deadlock reported on ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'db_mail',
@recipients='email_id@abb.com',
@subject = @subject,
@body = 'A deadlock has been recorded. Further information can be found in the attached file.',
@query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''deadlock-list'''' order by Id DESC)',
@query_result_width = 600,
@attach_query_result_as_file = 1
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: ?Msg 102, Level 15, State 1, Server SABBPROD034, Line 1
Incorrect syntax near 'deadlock
Existing script as below which is currently in SQL Job at step
if object_id('tempdb..##error') is not null drop table ##error
go
create table ##error
(
id int identity(1, 1),
logDate datetime,
processInfo varchar(20),
errorText nvarchar(max)
)
insert into ##error
exec master.dbo.sp_readErrorLog
select logDate, processInfo, errorText
from ##error
where id >=
(
select max(id)
from ##error
where errorText like '%deadlock encountered%'
)
declare @subject varchar(250)
select @subject = 'Deadlock reported on ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'db_mail',
@recipients='email_id@abb.com',
@subject = @subject,
@body = 'A deadlock has been recorded. Further information can be found in the attached file.',
@query = 'select logDate, processInfo, errorText
from ##error
where id >=
(
select max(id)
from ##error
where errorText like ''%deadlock encountered%''
)',
@query_result_width = 600,
@attach_query_result_as_file = 1
drop table ##error
November 3, 2016 at 6:14 am
Note there are four quotes on each side as its dynamic SQL and yours has two which would throw errors. That should fix
November 3, 2016 at 6:28 am
The same error repeated after removed quotes on both side..
@query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply