PowerShell Way: Automating SQL Server blocking capture, alert and notification

Capturing SQL Blocking is a challenge. Although there are lot of mechanisms out there to accomplish this task, not all of them are efficient and easy to implement. In an OLTP environment, database blocking is obvious, but excessive blocking reduces transaction throughput severely.  Often blockings become a nightmare and bring the server into a state where normal business activities suffer.

The goal of this PowerShell Script is to capture the blocking when it occurs along with all the details and to notify the concerned group immediately so that an appropriate corrective action can be taken quickly.  This script is lighting fast and very efficient in performing what it is supposed to do. The automation process has been developed by using PowerShell and DMVs. A windows task can be used to execute the script against any SQL Server from edition 2005 to 2014.

Download: http://bit.ly/1cZNScb.

The Script:

The main script (SQLBlocking-V2.ps1) contains a CSS style sheet, and two PowerShell functions:  “Send-EmailToSupport” and “Get-SQLBlocking”. There is one HTML formatting library function (written by Don Jones, MVP, www.powershell.org). At the bottom of the script, the main function“Get-SQLBlocking” has been called by providing different parameters and these are follows:

-Server = SQL Server instance

-CheckEverySeconds = How often script will check for blocking

-DurationToReport = Blocking duration to report.

-RunUntil = at what time it will stop.

How to execute?

We can create a Windows task to run the script. Once the script has started execution, it creates a connection to the database server and keep running in a loop for an allotted time while holding the connection. The script is using Windows Authentication while connecting to SQL Server.

Example:

The “SQLBlocking-V2.ps1” can be run directly from the PowerShell or we can run the script using Windows Tasks or SQL Agent Job. The following example will run for 24 hours checking every 20 seconds for blockings; if there is a blocking that occurs for more than 30 seconds, the script will catch the blocking and send an immediate email to the support group.

Steps:

1.      Create a Windows task from a local or remote machine.

2.      Schedule the task to start at 12:01AM.

3.      Call the “Get-SQLBlocking” as follows.

Get-SQLBlocking -Server 'SHB_MAIN\SQL2012' -CheckEverySeconds 20 -DurationToReport 30000 -RunUntil '11:55:00 PM'

Conclusion:

This article is on how to capture a SQL Server blocking for Alert/Notification purposes and this Automation (PowerShell Script) is well-tested against our heavy duty production server without any deficiencies. I am providing it “AS IS” and I hope that you will find it useful.