SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:
While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?
Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.
Below is the script that will help you with Deadlock detection and email in real time :
It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.
use dba_db ---- change HERE use find and replace as per your database name !!
go
/********************************************************************
Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled
*********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the job
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitoring - Deadlock Detector',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO Deadlock (
AlertTime,
DeadlockGraph
)
VALUES (
GETDATE(),
''$(ESCAPE_NONE(WMI(TextData)))''
)',
@database_name=N'dba_db',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send Email with Deadlock Graph] Script Date: 10/01/2010 12:01:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''<H3><FONT SIZE="3" FACE="Tahoma">Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately </FONT></H3>''
set @tableHTML = @tableHTML+ N''<table border="1">'' +
N''<FONT SIZE="2" FACE="Calibri">'' +
N''<tr><th align="center">RecordId</th>'' +
N''<th align="center">AlertTime</th>'' +
N''<th align="center">DeadlockGraph</th>'' +
N''</tr>'' +
ISNULL(CAST ( (
select td = '''',
td = RecordId,'''',
td = AlertTime,'''',
td = DeadlockGraph,''''
from dba_db.dbo.Deadlock where notified = 0
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ),'''') +
N''</FONT>'' +
N''</table>'' ;
-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!
exec master..xp_cmdshell ''BCP.exe "SELECT [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
-- send email out with the graph attached
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DBMAIL PROFILE'', ---- change HERE db mail profile !!
@recipients=''DBAcompanyGroup@companyName.com'', ---- change HERE email group!!
@subject = @subject1,
@body = @tableHTML,
@body_format = ''HTML'',
@file_attachments = ''D:\logs\Deadlock.xdl''; ---- change HERE deadlock graph location!!
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dba_db.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dba_db.dbo.Deadlock',
@database_name=N'dba_db',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Monitoring - Deadlock Detector' ;
GO
Yes, you can monitor for this event. There are a number of ways to do it and a number of third party apps that will be able to do this but you can handle all of it inside SQL Server.
You will want to create a stored procedure that reads the error log and captures the events you are interested in, and use that sp to send them out using dbmail. You will then need to create a SQL Agent job that calls this stored procedure at whatever interval you determine.
There are a number of good examples of how to do this, both create the stored procedure and the SQL Agent job so I won't go into the coding. Here is a good starter piece from the folks over at MSSQLTIPS Error log monitoring
xp_cmdshellcan trigger some security issues and there are recommendations on not to use it. Is there another way of saving the deadlock? Or sending it without previously psychically storing it on the drive? – Yaroslav Jul 25 '14 at 09:26dba_db.dbo.Deadlock. We have xp_cmdshell enabled in our environment and so I am using it. HTH. – Kin Shah Jul 25 '14 at 12:19