Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678

Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 12:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:28 PM
Points: 434, Visits: 869
Great article - very helpful!!!
Post #1167904
Posted Thursday, September 1, 2011 8:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 1,862, Visits: 3,607
JKG (8/31/2011)
Great article - very helpful!!!


Thank you :)


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168682
Posted Monday, January 21, 2013 9:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:35 PM
Points: 15, Visits: 68
Hi..

We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.

Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85)
2. SPID 87 is blocked by SPID 90
SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)

I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.
Post #1409775
Posted Wednesday, February 6, 2013 6:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 1,862, Visits: 3,607
krn045 (1/21/2013)
Hi..

We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.

Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85)
2. SPID 87 is blocked by SPID 90
SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)

I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.


Sometimes the blocking session is not doing anything, and there is therefore not much info on it (that info would be coming from sys.dm_exec_requests).

A good example of this would be a user-initiated session in which the user has opened a web page, and that has started a transaction that won't commit until the page is exited (code defect). In that scenario a SQL statement in the transaction could be blocking others indefinitely, even though there is no work being done.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1416779
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse