Email Sqlserver Block monitor script

Posted: March 19, 2011 in Sqlserver

Here is the Script which monitors and triggers mail to DBA with SPID and Code which got blocked by other  session id’s in any of the sqlserver databases.

This script gives in depth information to troubleshoot the blocking sessions.

/**—Create an Sqlserver Agent Job to report Blocking events and Mail to DBA team—-**/

declare @subj varchar(max),
@cmd varchar(max),
@file varchar(100)
select    @subj = @@servername  + ‘—Encountered Blocking sessions’,
@cmd = ‘Print ”Below listed Session_Id are blocked by.
————————————–


select r.Session_id as SID,
r.Start_time,
DBName = db_name(database_id),
r.Command,
substring(st.text,
r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1) As BlockedQuery,
r.Blocking_session_id,
s.Memory_usage,
s.Host_name,
s.Program_name,
s.Login_name
from sys.dm_exec_requests r
join sys.dm_exec_connections c on r.session_id = c.session_id
join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.session_id > 50 and r.blocking_session_id>1
order by r.session_id

exec msdb.dbo.sp_send_dbmail
@profile_name = ‘sql – dba’,
@recipients = ‘shamsul.hassan@hassanszone.com’,
@subject = @subj,
@body= ”,
@query = @cmd,
@query_result_separator = ‘|’,
@query_result_no_padding = 1

/**—Create an Sqlserver Alert to trigger Blocking alerts Mail to DBA team—-**/

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N’Request Blocked Processes’,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N”,
@notification_message=N”,
@event_description_keyword=N”,
@performance_condition=N’SQLSERVER:General Statistics|Processes blocked||>|1′,
@wmi_namespace=N”,
@wmi_query=N”,
—        @job_id=N’bc259ae4-d96b-4146-a372-1343e91cb5de’,
@job_name=’DBA_BlockingCheck_Report’
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s