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.