Bill Ramos, Principal Program Manager, SQL Manageability
In my previous blog post Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2 I showed how you can install SCOM 2007 R2 on a “bare-metal” VM using SQL Server 2008 R2 as the back end. One of the big features of the SQL Server 2008 R2 Management Pack is that we reduced the noise (or false alerts) reported by SCOM. This was done by disabling many of the alerts that we initially thought were critical/important and realized, it was better for you – the DBA – to determine what you think is important.
In this post, I’ll demonstrate how to enable and test one particular rule – Blocking Sessions – that is part of the SQL 2008 DB Engine set of rules.
This blog post is part of the series of posts that I’m using to prepare for my SQL PASS 2010 session on the SQL MP.
First, a little background on how to find out your blocking problems within SQL Server Management Studio. This is importance since once you find the blocking SQL Process ID (SPID) , you’ll want to probably kill the SPID and then fix the application or query that is causing the problem for the rest of your system.
The Activity – All Blocking Transactions report is available by right clicking on the Server of interest; select the Reports command; then the Standard Reports command; and finally selecting the Activity – All Blocking Transactions report. As you can see from the example above, you can get a real time view of the blocking problem with this report. The trick is, you need to run the report when the problem is happening to diagnose identify the problem SPID.
With Activity Monitor, you can quickly filter to the “Head Blocker” SPID by clicking on the [v] control for the Head Blocker column in the Processes section of the tool and select the value 1 – if present – to filter to the offending SPID. In the case shown above, the connection string information for the Application identifies the offending SPID as “BadApplication ver 1.2”. If only all problems were so easy to identify.
By right clicking on the highlighted row in Activity Monitor, you can issue the “Kill Process” command. If the problem reoccurs, you can consider using the APP_NAME() function within the Classifier Function for the Resource Governor feature introduced in SQL Server 2008 Enterprise Edition to block the application from connecting to the server.
Out of the box, after you have discovered the SQL Server computer using SCOM 2007 – see Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2. Once the system has cycled and SCOM agent is reporting health results to the Operations Console, you’ll see that even though a blocking situation is in progress, the monitor for the Microsoft SQL Server – Computers – indicates a Healthy state for the computer in question as shown below.
I happen to know that the highlighted server – BILLRAMO-KAT1 – is experiencing a blocking transaction problem. In order to detect the blocking scenario, you need to create an Override for the SQL Server MP.
To create and override for the blocking sessions scenario, you’ll need to do the following steps:
At this point, you need to go back to SSMS to kill the offending SPID using Activity Monitor. SCOM provides an easy way to get to SSMS by going to the Database Engines node under the Server Roles for Microsoft SQL Server. You can then click on SQL Management Studio in the Actions pane as shown below.
To launch SSMS with the proper connection context, select the instance path with the Database Engines list and then click on the SQL Management Studio action indicated by the big red arrows above. Then, launch the Activity Monitor, select the Head Blocker, and kill the session. Problem solved!
Now you know how to:
To learn more about the Blocking Sessions monitor and other monitors in the SQL Server Monitoring Management Pack, be sure to Download and read the SQLServerMPGuide.doc file.
Great article, is there a way of triggering or recording the blocking information. if the job completes before being killed is information stored anywhere ?
The publish is really the best on this laudable topic. I concur with your conclusions and will eagerly look forward to your future updates. Just saying thanks will not just be enough, for the exceptional lucidity in your writing. I will at once grab your rss feed to stay privy of any updates. De delightful work and much success in your business dealings!
<a href="http://www.seeksadmin.com">server management</a>