Post reply

Restore Database - But its in Use

  • September 1, 2010 at 10:25 pm

    #144950

    Hi,

    I want to restore my live database into my test database from last nights backup.

    The restore fails with :

    Msg 3101, Level 16, State 1, Line 2

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

    No database connections are listed in Activity monitor.

    sp_who2, lists a connection:

    16, BACKGROUND , sa, ., ., database_name, AUTOSHRINK ,3253844,1404363, 09/01 05:27:47

    I try to kill process 16 with kill 16 but I get the error:

    Server: Msg 6107, Level 14, State 1, Line 1

    Only user processes can be killed.

    I have previously restored this database today and it worked fine.

    What is the solution?

    MS SQL 2005

  • September 1, 2010 at 10:40 pm

    #1215397

    It looks like system background process was performing Auto-shrink, which did not allow you to have exclusive access to the database to restore.

    Disable Auto-shrink and try to restore.

  • September 1, 2010 at 10:51 pm

    #1215399

    I changed the auto-shrink option off.

    Still won't work.

    I have actually restored this database today already, and it worked fine.

  • September 2, 2010 at 12:03 am

    #1215417

    See... what you can do is when you kill the connections either you can put it in dbo mode before kicking off the restore or even simply delete the db and then start the refresh as anyway it will be overwritten.... even you can also try detaching the db delete the physical file and start the refresh... I hope this make sense....

    Rohit

  • September 2, 2010 at 12:10 am

    #1215419

    Hello,

    I think my problem lies in the fact that the AutoShrink operation is currently executing on the database I want to restore into.

    I guess I am just going to have to be patient and wait until it stops ( but it does seem to be taking a very long time).

    checking the sp_who2 16 ( where 16 is the process), i can see the IO stats changing so I assume the operation is still running.

    In the future, when I restore the database, Ill be setting the Auto_shrink property to off to prevent this in the future...

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

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