Post reply

SIngle User Mode - Database In Use

  • March 20, 2007 at 6:24 am

    #68471

    Hi

    I have set a Db in single user mode to effect a Db restore, and, as far as I can see, have closed all connections to the Db.

    sp_who and sp_who2 both show no connections to the database.

    However, when I attempt the restore, it fails with the error "Exclusive access could not be obtained because the database is in use"

    I am unable to see any active processes

    Any ideas gratefully received

    Many thanks

  • March 20, 2007 at 7:40 am

    #695921

    How are you doing the restore? Through EM or isql/osql or QA? EM opens multiple connections. The QA Object Browser opens a separate connection.

  • March 20, 2007 at 7:50 am

    #695927

    Run the following and see howmany connections u get

     

    select spid from master..sysprocesses where db_name(dbid) = 'Ur database name'

     

    Just kill the pocesses shown in this and then do the restore.

  • March 20, 2007 at 8:43 am

    #695959

    You can just take the database offline before starting the restore, and that will prevent connections.

    alter database MyDatabase set offline with rollback immediate

     

  • March 20, 2007 at 9:56 am

    #695986

    Thanks All

  • March 20, 2007 at 9:27 pm

    #696087

    We've had the same problem with SQL Server 2005 (not 2000).
    I think the database engine has a worker thread which repeatedly
    re-creates a hidden session. If you do your "set single_user with rollback"
    within the same session that you need to do the single-user operations,
    there's never a problem. If you set single user, then make a fresh connection
    to do the single-user operations, it'll fail sometimes only - depending on
    whether the worker thread has fired up in the meantime. The longer the
    time between closing one session and opening the next, the more
    frequently the failure occurs. So when we're doing database migrations,
    which consist of many scripts that must run in single user mode, each
    script must set single user mode with rollback, then, in order that the next
    script can start, must set multi user mode before exiting. It's a royal pain,
    so I'd very much like to know what this phantom thread is so we can
    disable it for the duration.
     
    Clifford Heath.

Viewing 6 posts - 1 through 6 (of 6 total)

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