In my previous blog I introduced a SQL Server feature, an Alert.  If you have not read through that blog, please take a few minutes and read it here.

In this blog I will show how to setup an Alert to notify you when a blocking situation occurs in SQL Server in your X3 database. Then, I will provide a simple blocking example in T-SQL code to test the Alert.  Of course, you will need to have already configured Database Mail on SQL Server and SQL Server Agent in order to receive the Alert notification. Also, if you plan to try these steps in your environment, please use your test environment.

From SQL Server locate SQL Server Agent, expand the folder for Alerts. Then right click Alerts and select New Alert...

Then provide a name for the alert. I used Blocked Proc.  Select the Type to be "SQL Server performance condition alert", Object: General Statistics, Counter: Processes blocked, Alert if counter: rises above and Value:0.  Also, don't forget to place a checkmark next to the Enable option.  Then click OK to save. When you are done you should have something that looks like this:

Next set up the Response page to look like this, using your own Operator:

Now that we have the blocked process alert, lets create a condition to test it.   Open two query windows in SQL Server.   In the first query window, I have created a query to create a dummy table named SageTest.  Execute just the block of code that creates the table.  Next, populate the SageTest table with a row and run a query to show it by highlighting the INSERT and SELECT statements.  Next, execute the BEGIN TRANSACTION statement along with the INSERT statement.  You can also follow it up by running a SELECT statement to see that your record is being inserted into the SageTest table.

Open a second query window. From this window, execute a SELECT statement against the SageTest table. You will see that after you execute this statement, it will not return any data from the SageTest table. This is because the BEGIN TRANSACTION that is running in Window #1, has blocked the query running in Window #2.

After a few moments, you should receive an email that looks like the following:

Be sure to go back to Window #1 and either COMMIT or ROLLBACK TRANSACTION a, to stop it from blocking. If you didn't change the default value for the Delay between responses, you will receive an email every few seconds.(This option is located under the Alert properties, Options page)  If you are trying to catch the times when blocking occurs, you should adjust the time interval to a value where you will be notified when it happens but not necessarily every second after on a continual basis. 

I hope you find value in using Alerts.  There are many others that can be selected and created in a similar manner. Please let me know if you have set up any and which ones you find helpful.