Send Mail When Blocking Occurs

[AshleyRickards]

Need to setup a job to check sysprocesses and send email when blocked > 0

TIA

Ashley L Rickards
SQL DBA

 

[AshleyRickards]

Something like this:


select blocked from dbo.sysprocesses -- where blocked > 0
IF sysprocesses.BLOCKED > 0

EXEC xp_sendmail @recipients = 'user@company.com',
@query = 'Select * from sysprocesses where blocked > 0',
@message = 'Processes are blocked on IPDOCKET01',
@subject = 'Processes are BLOCKED',
@attach_results = 'TRUE'



Ashley L Rickards
SQL DBA

 

[vongrunt]

I'm not sure using jobs + xp_sendmail is the right way to monitor problematic DB activity. Try with alerts instead (Type: SQL Server performance condition alert, Object: servername/Locks).

 

[AshleyRickards]

That might capture it .. I'm trying to track some bad code which is causing networkio so I need something more custom.

Ashley L Rickards
SQL DBA