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

Question

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

luissantos

SSCertifiable

Points: 7391
More actions
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

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

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

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.

Kory Becker Hall of Fame Points: 3585 More actions · Answer 2

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

dmanju Say Hey Kid Points: 685 More actions · Answer 3

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

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

SQLBill SSC Guru Points: 51440 More actions · Answer 5

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

Jayesh Antony Jose SSC Journeyman Points: 76 More actions · Answer 6

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

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.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

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."