Search This Blog

Sunday, March 9, 2008

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

I try to restore a new database in MSSQL 2005 and hit error
Restore Failed For Server 'xxxx' (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)


This is mostly caused by others user/session is using the database at the same time. To check whether got others user accessing the same database, execute SP_WHO will show who is using the database. Execute KILL to kill the process. In my case, i want to kill process with spid = 53

execute SP_WHO -- See which process in running

Execute KILL 53 -- 53 is the process id spid that i want to kill before restore.

In my case, i restore the database using script instead of using the SSMS.

Below is the script that i use to restore without any issue.


RESTORE DATABASE RateMyPosts
FROM DISK = 'C:\Documents and Settings\DEV1\Desktop\RatePosts.bak' WITH REPLACE,
MOVE 'RatePosts' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts.mdf',
MOVE 'RatePosts_Log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts_log.ldf'

3 comments:

pieeetr said...

A very easy work around is to go to services.msc, stop Microsoft CRM Asynchronous Processing Service, run the restore db, start the service again.

Do that and you should be ready to go.

Anonymous said...

Amiable dispatch and this post helped me alot in my college assignement. Say thank you you for your information.

Beyta said...

hello..

i want to kill the process from my own process (53).. but the sqlserver return 'cannot use KILL to kill your own process..'

the short way i use, i delete the already database.. then i restore with the new one...