locked
SQLMP cause SQLServers Lock? RRS feed

  • Question

  • Hi,

    Are anyone hear about SQL Management pack and SCOM cause SQL Locks?  I have recieved a strange incident from dba and said SCOM agent cause SQL locks? Before do any digging any ideas would be great.  Thank You.

    Thursday, May 6, 2010 10:00 AM

Answers

  • Hi,

     

    I don't meet this issue either.

     

    Please check if there are any related errors or events; if so, please also let us know in detail.

     

    Meanwhile, please also try the latest version of the SQL MP and double check your configuration referring to the SQL Server Management Pack Guide:

     

    Microsoft SQL Server Management Pack for Operations Manager 2007

    http://www.microsoft.com/downloads/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en

     

    Thanks.


    Nicholas Li - MSFT
    • Marked as answer by Nicholas Li Monday, May 24, 2010 3:48 AM
    Friday, May 7, 2010 8:35 AM
  • Any query can cause a lock on a table if a) the table is small and b) the query hits more than a goodly percentage of the rows in the table.  In SQL, remember, reads block writes - so if the SQL MP is doing a full table scan (or an index is in poor shape), SQL can escalate a table scan to a full table lock unless the read is done via a dirty read.

    On small tables, you will see the locks - but they are brief unless there is some other query blocking the read (a write in progress will block a read that has been scheduled).  So it isn't unusual for the eagle-eye DBA to be able to show "hey, there is locking happening in the database".  Locks is part of how SQL does its work.  Unless these locks are causing a problem, consider this just the way SQL works.  If they cause a problem, it takes deeper analysis to figure out why a lock is a) happening and b) what is preventing a lock from being released.


    Microsoft Corporation
    • Marked as answer by Nicholas Li Monday, May 24, 2010 3:48 AM
    Thursday, May 13, 2010 3:32 PM

All replies

  • i'm pretty sure it doesn't, but it could happen i suppose. (well the agent can't, but sql scripts could).

    i'd really like to see some more information about the lock.


    Rob Korving
    http://jama00.wordpress.com/
    Thursday, May 6, 2010 10:56 AM
  • They said roughly it's about dbspace (I'm guessing It's the GetSQL2005DBSpace.js script) and another unknow sp execute .  I'll provide more information if possible. Thank you.
    Thursday, May 6, 2010 12:44 PM
  • Hi,

     

    I don't meet this issue either.

     

    Please check if there are any related errors or events; if so, please also let us know in detail.

     

    Meanwhile, please also try the latest version of the SQL MP and double check your configuration referring to the SQL Server Management Pack Guide:

     

    Microsoft SQL Server Management Pack for Operations Manager 2007

    http://www.microsoft.com/downloads/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en

     

    Thanks.


    Nicholas Li - MSFT
    • Marked as answer by Nicholas Li Monday, May 24, 2010 3:48 AM
    Friday, May 7, 2010 8:35 AM
  • Any query can cause a lock on a table if a) the table is small and b) the query hits more than a goodly percentage of the rows in the table.  In SQL, remember, reads block writes - so if the SQL MP is doing a full table scan (or an index is in poor shape), SQL can escalate a table scan to a full table lock unless the read is done via a dirty read.

    On small tables, you will see the locks - but they are brief unless there is some other query blocking the read (a write in progress will block a read that has been scheduled).  So it isn't unusual for the eagle-eye DBA to be able to show "hey, there is locking happening in the database".  Locks is part of how SQL does its work.  Unless these locks are causing a problem, consider this just the way SQL works.  If they cause a problem, it takes deeper analysis to figure out why a lock is a) happening and b) what is preventing a lock from being released.


    Microsoft Corporation
    • Marked as answer by Nicholas Li Monday, May 24, 2010 3:48 AM
    Thursday, May 13, 2010 3:32 PM