Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Script to know Disconnected session holding exclusive lock

Answered Script to know Disconnected session holding exclusive lock

  • Sunday, December 09, 2012 2:37 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I am prequently getting  alerts stating "Disconnected Sessions Holding Exclusive DB Locks".

    i checked in the active monitor and sp_who2, sp_lock. but i was unable to find the disconnected sessions locks.

    Please provide suggestion to find this. and also provide me if any script available to check this.

    Thanks

    K

     

All Replies

  • Sunday, December 09, 2012 2:44 AM
    Moderator
     
     
    Sign In to Vote
    0

    What is the source of alerts? Is there any job which is killing the sessions and sending alerts? Please check ERRORLOG and see if you are seeing KILL command there. May be, you didn't see them in activity monitor because they were already disconnected (as per alert name)?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

     
  • Sunday, December 09, 2012 2:59 AM
     
     Answered
    Sign In to Vote
    0

    Hi Balmukund,

    Yes you are correct. There is a job which runs for every 2 min and kills the inactive session. but alerts are sent monitoring system i.e nimsoft.

    I can able to see Kill command for few sessions from the error log.

    Please suggest me the next step?

    Thanks

    K

    • Marked As Answer by K735 Wednesday, December 12, 2012 9:48 PM
    •  
     
  • Sunday, December 09, 2012 5:35 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I would like to know why the disconnected session would hold exclusive locks?

    Thanks

    K

     
  • Sunday, December 09, 2012 12:03 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi All,

    I would like to know why the disconnected session would hold exclusive locks?

    Thanks

    K

    My suggestion would be to contact NimSoft support.
    If you don't want to do that then I think you can put profiler on the server and find the query run by the tool.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter