Post reply

error "database in use" when trying RESTORE or DETTACH my database

  • October 19, 2005 at 12:31 pm

    #166415

    Hello

    I have a problem when i try to RESTORE or DETTACH a database.

    The error is "Database is in use" and the only SPID is 1

    I try to KILL all process in this database but MSDE report to me that could´t not kill this process.

    I would like to know how this error occur and how can resolve them.

    Many thanks

    Luis Santos

  • October 19, 2005 at 1:46 pm

    #598458

    Use sp_who to find the SPID. If you are restoring from QA, check that your are not running from the same database. Change db to MASTER. If you have Enterprise Manager open, click on a different database to remove the connection to the database that you are restoring.

     

     

  • October 19, 2005 at 4:57 pm

    #598531

    Sounds like you are you trying to kill your own session?

  • October 19, 2005 at 5:39 pm

    #598538

    Try throwing the database into single user mode and from the same connection restore the database.

  • October 20, 2005 at 2:02 am

    #598586

    start with :

    ALTER DATABASE yourdb Set RESTRICTED_USER , read_only WITH ROLLBACK IMMEDIATE

    Restore database yourdb from .....

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 20, 2005 at 7:03 am

    #598675

    I agree with SQL Junkie....it sounds like you are connected to the database you are trying to detach. Connect to another database and then try to detach it......

    USE Master

    EXEC SP_DETACH_DB 'pubs'

    -SQLBill

  • October 21, 2005 at 2:46 am

    #599034

    If still you are not able to do the RESTORE...

    Try putting in single user mode using NO_WAIT

    ALTER DATABASE pubs RESTRICTED_USER/SINGLE_USER WITH NO_WAIT

     

    Jayesh

  • October 21, 2005 at 1:58 pm

    #599297

    Also, when you detach, through EM, it gives you the option to kill all the connections.  You can use that feature even if you don't really want to detach. Kill the connections, then cancel out of the detach screen and run your restore instead.

  • October 24, 2005 at 11:57 am

    #599611

    SPID 1 is the 'lazy writer' - a system, and non-killable process. It sounds as if you need to issue the following conmmands prior to your detach:

     

    use your_database

    go

    checkpoint

    go

    use master

    go

    exec sp_detach your_database

    go

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 9 posts - 1 through 8 (of 8 total)

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