VirtualObjectives

Check Blocking (using SaveTableAsHTML)

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 another version which does not use SaveTableAsHTML however there is a lot of fiddling around with producing an email containing a HTML table.

Requirements:

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

Deployment:

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

Script: