Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 Alerts for blocking Expand / Collapse
Author
Message
Posted Monday, March 12, 2012 6:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 6:15 PM
Points: 38, Visits: 89
Does anyone know of a script to use for an alert in sql 2008 standard for Extreme Deadlocks (blocking).
I have the use of quest tools, spotlight in particular. But I need to be proactive I need to send an alert for extreme locks causing blocking.
This alert needs to be emailed to our helpdesk, does anyone have any advice.
Regards,
Flyfish
Post #1265650
Posted Tuesday, March 13, 2012 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:17 AM
Points: 321, Visits: 592
Hmm..

There may be a way to do this using triggers etc.. But you can also do it by running a schedule every x minutes to check for blocking.

Create a teble to hold the contents of sp_who, then test

--Drop the temp table
DROP TABLE #who

--Create a temp table to store the contents of sp_who
CREATE TABLE #who
(spid INTEGER,
ecid INTEGER,
status VARCHAR(1000) NULL,
loginname SYSNAME NULL,
hostname SYSNAME NULL,
blk INTEGER,
dbname SYSNAME NULL,
cmd VARCHAR(1000) NULL,
request_id INTEGER)

--Get the data from sp_who and insert it into the temp table.
INSERT INTO #who EXEC sp_who

--Find the blocking session
SELECT w2.*
FROM #who w1
INNER JOIN #who w2 ON w1.blk = w2.spid
WHERE w1.blk <> 0

Then you can create a new alert and test if there is blocking and then use dbmail to send an email alert:


--Test if there is blocking
IF (SELECT COUNT(*)
FROM #who w1
INNER JOIN #who w2 ON w1.blk = w2.spid
WHERE w1.blk <> 0) <> 0
BEGIN

--Send an email alerting the helpdesk there is blocking
EXEC msdb.dbo.sp_send_dbmail @profile_name=N'SQLAlerts',@recipients=N'recipient@emailserver.com',@Subject=N'Blocking Detected',
@Body=N'There appears to be blocking on your server',
@query='SELECT w2.*
FROM #who w1
INNER JOIN #who w2 ON w1.blk = w2.spid
WHERE w1.blk <> 0'
END

However I assume you're going to want to know as soon as blocking occurs?

Cheers,

Jim.


SQL SERVER Central Forum Etiquette
Post #1266104
Posted Wednesday, March 14, 2012 12:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 12:37 AM
Points: 2, Visits: 84
set nocount on;

declare @bufferSPID table (EventType nvarchar(30), Parameter smallint, EventInfo nvarchar(4000));
declare @bufferBLOCKED table (EventType nvarchar(30), Parameter smallint, EventInfo nvarchar(4000));
declare @ret table (spid int, spidEventInfo nvarchar(4000), blocked int, blockedEventInfo nvarchar(4000), hostname varchar(100), loginame varchar(100));

DECLARE @ospid int,
@oblocked int,
@ohostname varchar(100),
@ologinname varchar(100),
@spidEventInfo nvarchar(4000),
@blockedEventInfo nvarchar(4000)
DECLARE indexCursor CURSOR FOR
select spid, blocked, hostname, loginame
from sys.sysprocesses
where [status] = 'suspended' -- tasks that are waiting for I/O to complete
OPEN indexCursor
FETCH NEXT FROM indexCursor INTO @ospid, @oblocked, @ohostname, @ologinname
WHILE (@@FETCH_STATUS = 0)
BEGIN

delete from @bufferSPID
delete from @bufferBLOCKED

if(@ospid <> 0) insert into @bufferSPID exec ('DBCC INPUTBUFFER(' + @ospid + ')');
if(@oblocked <> 0) insert into @bufferBLOCKED exec ('DBCC INPUTBUFFER(' + @oblocked + ')');

select @spidEventInfo= EventInfo from @bufferSPID
select @blockedEventInfo = EventInfo from @bufferBLOCKED

insert into @ret (spid, spidEventInfo, blocked, blockedEventInfo, hostname, loginame)
select @ospid, @spidEventInfo, @oblocked, @blockedEventInfo, @ohostname, @ologinname

FETCH NEXT FROM indexCursor into @ospid, @oblocked, @ohostname, @ologinname
END
CLOSE indexCursor
DEALLOCATE indexCursor


select * from @ret
Post #1266511
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse