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.
- SQL Server 2008R2 and above.
- Database Mail.
- Agent Manager
- Stored procedure SaveTableAsHTML
- Download and install stored procedure SaveTableAsHTML.
- Copy the script below.
- Create a new job in SQL Server Agent called something like "Check Blocking".
- Add the script and change the configuration variables to suit you requirements.
- Schedule the job to reoccur at a suitable interval but no less than the @MinutesBlocked variable.