VirtualObjectives

Check Blocking

This script allows you to check for blocking activity taking longer than N number of minutes and send an email containing the connection details of all those affected.

There is a lot of fiddling around with producing an email containing a HTML table.
There is also another version which uses SaveTableAsHTML to produce better results.

Requirements:

  • SQL Server 2008R2 and above.
  • Database Mail.
  • Agent Manager

Deployment:

  1. Copy the script below.
  2. Create a new job in SQL Server Agent called something like "Check Blocking".
  3. Add the script and change the configuration variables to suit you requirements.
  4. Schedule the job to reoccur at a suitable interval but no less than the @MinutesBlocked variable.

Script: