Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts

  • fannonland2

    Valued Member

    Points: 65

    Just trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    fannonland2 (2/28/2011)


    Just trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?

    Yes, in fact this is the power SCOM gives you. You create a rule for SQL 2000 and one for SQL 2005/8 (it should be able to accomodate both of these versions).

    Then SCOM will propagate these scripts to all servers on which a SCOM agent has been deployed and that will be it.

    The more servers you have in your environment, the larger the benefit of the centralized management SCOM provides.

    Actually, to be precise, there are a couple of rules for each SQL version, if I remember correctly: one for detecting the condition and one for generating the alert.

    But it is the same idea.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • fannonland2

    Valued Member

    Points: 65

    Hmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis".

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    fannonland2 (3/2/2011)


    Hmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis".

    Yes, sorry the instructions on ReadMe.txt file are not very clear.

    The Analysis db could be any centralized database in your environment that is collecting historical blocking information.

    It is not the SCOM database, but a database created by you to collect the custom info generated by the rules here.

    If you already have such a db in your environment for collecting other such info, you can use the same db for this implementation.

    You may need to edit the name of the database from "Analysis" to whatever the name of your db is in a few places though.

    Does that make sense?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • fannonland2

    Valued Member

    Points: 65

    ok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?

    And then when I need to customize it it will be...

    ...

    sAnalysisInstance = "scomserver\scomsql"

    sAnalysisDB = "analysis"

    ...

    ...

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

    ...

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    fannonland2 (3/2/2011)


    ok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?

    And then when I need to customize it it will be...

    ...

    sAnalysisInstance = "scomserver\scomsql"

    sAnalysisDB = "analysis"

    ...

    ...

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

    ...

    Yes, that sounds right. Basically, you need to specify a place where you will be storing all the info collected by the custom rules.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • fannonland2

    Valued Member

    Points: 65

    I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.

    I get this...

    sAnalysisInstance = "SCOMP001\SCOMSQL"

    sAnalysisDB = "Analysis"

    But I don't get this....

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

    What do I need to change in this line???

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    fannonland2 (3/2/2011)


    I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.

    I get this...

    sAnalysisInstance = "SCOMP001\SCOMSQL"

    sAnalysisDB = "Analysis"

    But I don't get this....

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

    What do I need to change in this line???

    sAnalysisInstance and sAnalysisDB take their values from the variable definitions so you don't have to worry about changing those.

    UID: this is a server login and database user on Analysis db that has execute permissions on stored procedure SCOM_InsertBlockingInfo.

    This procedure is installed on Analysis db.

    See below code excerpt to get an idea of what I am talking about:

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

    cnAnalysisInstance.Open sConnString

    Set cmd = CreateObject("ADODB.Command")

    Set cmd.ActiveConnection = cnAnalysisInstance

    cmd.CommandText = "[dbo].[SCOM_InsertBlockingInfo]"

    cmd.CommandType = 4 'adCmdStoredProc

    In my case I have a login, ssrs, mapped to a database role, ROLE_ssrs, in the Analysis db.

    ROLE_ssrs has EXECUTE permissions on procedures SCOM_InsertBlockingInfo and SCOM_InsertBlockingInfo_2000.

    Then the line you were asking about could read like this:

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=ssrs;PWD=insert-complex-passwd"

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • fannonland2

    Valued Member

    Points: 65

    ok, changed that line back to

    iWaitInMinutes = 1

    iAlertFreq = 1

    sAnalysisInstance = "server\instance"

    sAnalysisDB = "dbName"

    and the other line is now....

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"

    But I have yet to receive a single notification.

    I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    fannonland2 (3/3/2011)


    ok, changed that line back to

    iWaitInMinutes = 1

    iAlertFreq = 1

    sAnalysisInstance = "server\instance"

    sAnalysisDB = "dbName"

    and the other line is now....

    sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"

    But I have yet to receive a single notification.

    I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?

    Try simulating blocking by running the following in SSMS:

    In 1st window; run this in a test database of your choice with a table of your choice:

    USE dbName;

    set nocount on

    begin tran

    while 1=1

    update tblName set test ='abcd' --

    --rollback tran

    In a 2nd window of SSMS:

    --2nd window:

    USE dbName;

    SELECT * FROM tblName

    That will establish a blocking condition where the 2nd query is blocked indefinitely by the 1st.

    Now on a 3rd SSMS window run the following.

    Run the attached query after a minute, do you see any blocking? (this is the same query running in the .vbs script for SQL2005/2008 servers).

    BTW, I hope you imported the custom management pack into SCOM... 🙂

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • JKG

    SSCommitted

    Points: 1503

    Great article - very helpful!!!

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    JKG (8/31/2011)


    Great article - very helpful!!!

    Thank you 🙂

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • krn045

    Right there with Babe

    Points: 775

    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.

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 14 posts - 61 through 74 (of 74 total)

You must be logged in to reply to this topic. Login to reply