locked
Blocking SPIDS Notification RRS feed

  • Question

  • In MOM 2005 the alert for blocking spids showing the details of the blocking spids.

    ex:


    MOM 2005 Blocking Alert:
    Severity:  Critical Error
    Status:  New
    Source:  SQL Server 2005 Service Availability
    Name:  SQL Server 2005 Block Analysis (MSSQLSERVER.HR90DMO)
    Description:  The program "PeopleSoft" has been blocked for 1.40285 minutes on database HR90DMO in the SQL instance MSSQLSERVER.  The defined acceptable blocking threshold is 1 minute(s).  "PeopleSoft" is running on SPID 179 as login PSADM and is blocked by SPID 193.  The resource id is KEY: 17:72057595709882368 (ff0057505ff7)                                                                                 

    How do I get that type of data in my OPMSGR 2007 notifications? below is all I get today.

    SCOM 2007:

    Alert: Microsoft.SQLServer.2005.DBEngine.BlockedSPIDsMonitor
    Source: SQLBUS
    Path: SQLP1CLUSV1.njmgroup.com
    Last modified by: System
    Last modified time: 8/4/2009 7:22:16 AM
    Alert description:  

     

    Any help would be greatly appreciated.



    Thanks

    Paul

    Tuesday, August 4, 2009 7:43 PM

Answers

  • The Blocking SPID monitor in the SQL 2005 MP called Blorcking SPIDs, however, the GeneratesAlert parameter is set to FALSE by default. Use an override to set this to TRUE. Also, override the number of blocked processes and duration. Default settings are 1 process for 1 minute. 

    However, the alert description is empty and cannot be overridden given this is a sealed MP.

    There is an alternate solution for Blocked SPID monitoring put together by a DBA. See the following articles for details http://www.sqlservercentral.com/articles/Blocking/64038/


    Pete Zerger, MVP-OpsMgr and SCE | http://www.systemcentercentral.com
    Tuesday, August 4, 2009 8:22 PM

All replies

  • The Blocking SPID monitor in the SQL 2005 MP called Blorcking SPIDs, however, the GeneratesAlert parameter is set to FALSE by default. Use an override to set this to TRUE. Also, override the number of blocked processes and duration. Default settings are 1 process for 1 minute. 

    However, the alert description is empty and cannot be overridden given this is a sealed MP.

    There is an alternate solution for Blocked SPID monitoring put together by a DBA. See the following articles for details http://www.sqlservercentral.com/articles/Blocking/64038/


    Pete Zerger, MVP-OpsMgr and SCE | http://www.systemcentercentral.com
    Tuesday, August 4, 2009 8:22 PM
  • The Blocking SPID monitor in the SQL 2005 MP called Blorcking SPIDs, however, the GeneratesAlert parameter is set to FALSE by default. Use an override to set this to TRUE. Also, override the number of blocked processes and duration. Default settings are 1 process for 1 minute. 

    However, the alert description is empty and cannot be overridden given this is a sealed MP.

    There is an alternate solution for Blocked SPID monitoring put together by a DBA. See the following articles for details http://www.sqlservercentral.com/articles/Blocking/64038/


    Pete Zerger, MVP-OpsMgr and SCE | http://www.systemcentercentral.com

    I was wondering if anyone knows if Microsoft intends to fix this problem in a future MP.  I have downloaded the lates SQL MP and it's still broken.
    Friday, August 28, 2009 7:23 PM
  • I'll make sure that the feedback get's to the SQL team for their consideration in a future release.  As a side note, the intent behind the design is discussed in another thread.

    If you do choose to enable alerting on the monitors that are shipped then you'll probably want to be aware of the procedures that you can follow to customer alert details via override, as it's not really straight forward.  Check out the following posts on Marius' blog for more details:
     -- http://blogs.msdn.com/mariussutara/archive/2008/07/15/monitor-from-sealed-mp-generated-alert-and-i-do-not-like-its-description.aspx
     -- http://blogs.msdn.com/mariussutara/archive/2008/07/17/dynamic-alert-settings-for-monitor-which-never-had-any-done-thru-overrides.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights. Use of attachments are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Monday, August 31, 2009 4:11 PM
  • Cory -
    I'm having the same issue, and have been spending a lot of time trying these work-arounds without success.  I am a DBA and not a MOM/SCOM developer.  The MOM 2005 monitor was perfect. This is not the first post on this topic.  Is there any easy way to get this information in the description?  Pls communicate to the SQL Team how important it is to have the blocking details.  I appreciate your help.

    Thanks,
    ltx

    Monday, August 31, 2009 8:13 PM
  • I don't know if this helps but you can see the details in the health explorer.
    Monday, August 31, 2009 8:26 PM
  • I tried Mario's script and it works for most of my servers. I got stuck with one server with the following problem.

    The vbscript that runs in the Rule is (I only put the relevent stuff below):

    Set objParameters = WScript.Arguments
    sManagedInstance = objParameters(0)
    Set objParameters = Nothing
    
    iWaitInMinutes = 10
    iAlertFreq = 10
    
    SCRIPT_SQL = _
            "SET NOCOUNT ON; " & _
            "" & _
            "CREATE TABLE #blocks " & _
            "( " & _
    	    "   SPID SMALLINT " & _
            ",	BlockingSPID SMALLINT " & _
            ",	WaitTime BIGINT " & _
            ",	LastWaitType VARCHAR(32) " & _
            ",	WaitResource VARCHAR(32) " & _
            ",	DBName VARCHAR(100) " & _
            ",	CPU_Time INT " & _
            ",	Total_Elapsed_Time INT " & _
            ",	Physical_IO BIGINT " & _
            ",	Logical_Reads BIGINT " & _
            ",	Memory_Usage INT " & _
            ",	Login_Time DATETIME " & _
            ",	[Status] VARCHAR(30) " & _
            ",	HostName VARCHAR(128) " & _
            ",	ProgramName VARCHAR(128) " & _
            ",	Command VARCHAR(16) " & _
            ",	NT_User_Name VARCHAR(128) " & _
            ",	Login_Name VARCHAR(128) " & _
            ",	SQL_Text VARCHAR(1000) " & _
            "); " & _
            "" & _
            "INSERT INTO " & _
    	    "   #blocks " & _
            "SELECT " & _
    	    "   R.session_id " & _
            ",	R.blocking_session_id " & _
            ",	R.wait_time " & _
            ",	R.last_wait_type " & _
            ",	R.wait_resource " & _
            ",	DB_NAME(R.database_id) " & _
            ",	R.cpu_time " & _
            ",	R.total_elapsed_time " & _
            ",	R.reads + R.writes " & _
            ",	R.logical_reads " & _
            ",	S.memory_usage " & _
            ",	S.login_time " & _
            ",	R.status " & _
            ",	S.[host_name] " & _
            ",	S.[program_name] " & _
            ",	R.command " & _
            ",	S.nt_user_name " & _
            ",	S.login_name " & _
            ",	LEFT(H.text, 1000) " & _
            "FROM " & _
            "	sys.dm_exec_requests R " & _
            "INNER JOIN " & _
            "	sys.dm_exec_sessions S " & _
            "ON " & _
            "	R.session_id = S.session_id " & _
            "CROSS APPLY  " & _
            "	sys.dm_exec_sql_text(R.sql_handle) H " & _
            "WHERE " & _
            "	R.session_id > 50 " & _
            "	AND  " & _
            "	( " & _
            "		(R.blocking_session_id <> 0 AND R.wait_time > " & CStr(iWaitInMinutes*60) & " * 1000 )" & _
            "		OR  " & _
            "		(R.blocking_session_id = 0 AND R.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 )) " & _
            "	) " & _
            "ORDER BY  " & _
            "	R.blocking_session_id;" & _
            "" & _
            "IF EXISTS (SELECT * FROM #blocks WHERE BlockingSPID > 0) " & _
            "BEGIN " & _
    	    "   SELECT * FROM #blocks " & _
    	    "   WHERE BlockingSPID > 0 " & _
    	    "   OR SPID IN (SELECT BlockingSPID FROM #blocks WHERE BlockingSPID > 0); " & _
            "END " & _
            "ELSE " & _
            "BEGIN " & _
    	    "   TRUNCATE TABLE #blocks; " & _
    	    "   SELECT * FROM #blocks; " & _
            "END " & _
            "" & _
            "DROP TABLE #blocks; "
    
    Set cnManagedInstance = CreateObject("ADODB.Connection")
    cnManagedInstance.Provider = "sqloledb"
    cnManagedInstance.ConnectionTimeout = 30
    sConnString = "Server=" & sManagedInstance & ";Database=master;Trusted_Connection=yes"
    
    cnManagedInstance.Open sConnString
    Set rsBlockedSPIDS = cnManagedInstance.Execute(SCRIPT_SQL)
    
    Do While Not rsBlockedSPIDS.EOF
        
        .... DO SOMETHING....
        rsBlockedSPIDS.MoveNext
        
    Loop
    


    I get an error which fails on the line of the query execution:
    Microsoft OLE DB Provider for SQL Server: Incorrect syntax near '.'. which is pointing to the line of code:
    Set rsBlockedSPIDS = cnManagedInstance.Execute(SCRIPT_SQL)
    

    What's weird is that if I ran the query itself in Management Studio while creating the locks manually, the query works and I am able to get information returned from the temp table. For whatever reason, in script format, the query doesn't work.

    Thursday, November 12, 2009 10:35 AM