Marios Philippopoulos did a great job with an article on monitoring long-running SQL blocks with SCOM 2007 at SQLServerCentral.com. The article: Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts (http://www.sqlservercentral.com/articles/Blocking/64038/) provides a set of scripts for detecting (and display vital information about) long-running blocks in SQL Server 2000 and 2005 as well as a walkthrough on configuring the SCOM elements for alerting. This is an absolute life-saver and can really cut down on troubleshooting time when dealing with application issues caused by problematic SQL blocking.
The only customizations I’ve made to this monitoring configuration when implementing are some changes to the SQL script to tune the block duration threshold as well as increase the size of some of the type definitions in the temp table to allow for longer command name output.
I’d highly recommend this article to anyone using SCOM to monitor SQL Server.
Filed under: SCOM, SQL Server, System Center Operations Manager
