Hidden connections

  • I am having a strange problem that has shown up sporadically on a handful of servers in the past several months.  

    I'll have a database that I want to detach or restore over. The error message will come back saying that it can't obtain exclusive access.  So, I'll run sp_who.  I won't see any connections to the database, but for some reason, SQL Server will continue to give the same error message.  The only work-around has been to restart SQL Server.

    Until the last week this hasn't been a huge problem and was seen only on non-production servers. Now it is happening on a production box. I have a job that runs each night. The first step kills all connections to the database. The second step performs a restore.  It has worked perfectly for almost two years. Now, this week, the job has failed 3 times out of 7.  Again the error is that it can't obtain exclusive access.

    This particular server had SQL Server SP4 applied shortly before the problem started.  Any ideas. 

    Please don't tell me to run sp_who and kill the connections. I am already doing that.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I used to have an issue like this when I was using quest spotlight to monitor some of my servers, I would issue a kill command from a script but by the time it had finished the app had connected again. I set up a job to stop the app whenever I was going to do this kind of work.

  • That is a good place to start looking. I'll see if I can just exclude this particular database or something.  Thanks for the help.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I decided to try this command instead of the script to kill connections:

    ALTER DATABASE db_name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

Viewing 4 posts - 1 through 3 (of 3 total)

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