April 18, 2011 at 7:04 am
I've setup an alert that executes a job with a single stored procedure that collects blocking information and emails the results. But, it won't fire until after the blocking clears, not when blocking is occurring for more than 1 second (for testing purposes). It happens on both a server and my laptop. :unsure:
server:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
laptop:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--Alert--------------------------------------------------
USE [msdb]
GO
/****** Object: Alert [DBA_LockingTreshold] Script Date: 04/18/2011 08:50:23 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBA_LockingTreshold',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=10,
@include_event_description_in=1,
@notification_message=N'DBA_LockingTreshold alert',
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Lock Wait Time (ms)|_Total|>|1',
@job_id=N'ba5a8cfe-cc92-43d2-907f-b5d0d432e956'
GO
--Procedure--------------------------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_SendSPIDBlockingReport] Script Date: 04/18/2011 08:53:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------------
--Created by Clay Punnett 4/15/10 v 1.0.0
--Script selects and reports blocking SPIDS Information.
-----------------------------------------------------------------------------------
ALTER Procedure [dbo].[sp_SendSPIDBlockingReport]
as
Set Nocount On
Declare @tableHTML nvarchar(MAX), @ServerName varchar(50), @BlockingCount int, @BlockingSPID int, @BlockedSPID int
Select @ServerName = CAST(SERVERPROPERTY('MachineName') as varchar(50))
Select @BlockedSPID = SPID
From master.dbo.sysprocesses (nolock)
Where blocked > 0
Select @BlockingSPID =
blocking_session_id
From sys.dm_os_waiting_tasks (nolock)
Where blocking_session_id is not null
Create Table #InputBuffer (
EventType nvarchar(30) NULL
, [Parameters] int NULL
, EventInfo nvarchar(4000)
)
Insert Into #InputBuffer (EventType, [Parameters], EventInfo)
Exec('DBCC InputBuffer(' + @BlockingSPID + ') with no_infomsgs')
--select @BlockedSPID as BlockedSPID, Wait_Duration_ms/1000 as WaitTimeInSecs, DB_NAME(dbid) as DatabaseName
--, @BlockingSPID as BlockingSPID, EventInfo as BlockingQuery
--from #InputBuffer
--join sys.dm_os_waiting_tasks on @BlockedSPID = Session_ID
--join master.dbo.sysprocesses on @BlockedSPID = SPID
Begin
Set @tableHTML =
N'<H1>Production Blocking Report</H1>' +
N'<table border="3" bordercolor="red">' +
N'<tr><th>ServerName</th><th>BlockedSPID</th>' +
N'<th>WaitTimeInSecs</th><th>DatabaseName</th>' +
N'<th>BlockingSPID</th><th>BlockingQuery</th></tr>' +
CAST((Select td = @ServerName, ''
, td = @BlockedSPID, ''
, td = Wait_Duration_ms/1000, ''
, td = DB_NAME(dbid), ''
, td = @BlockingSPID, ''
, td = EventInfo, ''
From #InputBuffer
Join sys.dm_os_waiting_tasks on @BlockedSPID = Session_ID
Join master.dbo.sysprocesses on @BlockedSPID = SPID
For XML PATH('tr'), TYPE) as nvarchar(MAX)) +
N'</table>';
End
Begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Your.email@email.com',
@subject = 'Production Blocking Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'SQLMAIL',
@importance = 'High';
End
There is an exception to every rule, except this one...
April 18, 2011 at 7:55 am
Since blocking is a transient operation you can't be guaranteed the blocking will still be in place when a process runs. You might want to look into using event notifications to get the data you are looking for. One of the events you can notify on is the BLOCKED_PROCESS_REPORT.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 18, 2011 at 8:10 am
I do appreciate the work-around idea. But, I am still very curious as to why alerts aren't firing when the blocking threshold is reached.
There is an exception to every rule, except this one...
April 18, 2011 at 11:15 am
Surely, someone's seen/heard of this issue before.
There is an exception to every rule, except this one...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply