May 10, 2011 at 9:54 am
Using sql server 2008, I'm getting this message when trying to Restore :
"exclusive access could not be obtained because the database is in use"
This is a live database for an E-Commerce store. What is the best way to fix this? Thanks
May 10, 2011 at 10:06 am
set single user with rollback immediate
restore.
set multi-user
May 10, 2011 at 10:14 am
thanks . . Is this the way to do it :
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE; << do I need this ??
** RESTORE DATABASE **
ALTER DATABASE AdventureWorks
SET MULTI_USER;
May 10, 2011 at 10:16 am
MikeBlockhead (5/10/2011)
WITH ROLLBACK IMMEDIATE; << do I need this ??
No, but if there are any active sessions which are using the database, the ALTER DATABASE will wait for these to end if you omit WITH ROLLBACK.
May 10, 2011 at 10:18 am
Yes, that will force all pending transactions to be cancelled and rolled back. Then you'll be able to run the restore.
May 10, 2011 at 10:21 am
Nils Gustav Stråbø (5/10/2011)
MikeBlockhead (5/10/2011)
WITH ROLLBACK IMMEDIATE; << do I need this ??No, but if there are any active sessions which are using the database, the ALTER DATABASE will wait for these to end if you omit WITH ROLLBACK.
When I restore a db I know I'll be losing data, so I don't care if I lose whatever's going on right now so there's no point to wait. I preffer to shut everyone out ASAP and get the db back online sonner rather than later.
May 10, 2011 at 10:22 am
You have to ROLLBACK IMMEDIATE or one of the other options listed in the following article:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 9:53 am
I tried :
ALTER DATABASE AdventureWorks
SET SINGLE_USER
...
But it gives me the same "Exclusive access could not be... " error.
When I set the database to Single User using the the menu
(r click, Properties, Option, at the bottom) it worked.
Why is that?
May 11, 2011 at 9:57 am
Have you used the rollback immediate option?
May 11, 2011 at 9:58 am
Have you checked the connections to the Database?
Are there jobs running, user connections or open transactions?
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 10:46 am
Ninja's_RGR'us (5/11/2011)
Have you used the rollback immediate option?
Yes
>> Have you checked the connections to the Database?
I did "SELECT SYSTEM_USER" and it was my Windows Log IN
>> Are there jobs running, user connections or open transactions?
Don't know how to do that.
May 11, 2011 at 11:00 am
You can use sp_who or sp_who_2 or sp_who3, or the DMV's and DBCC OPENTRAN.
Listed in the following URL is an article that contains a Stored that references the system tables:
http://sqlserverplanet.com/dmv-queries/a-better-sp_who2-using-dmvs-sp_who3
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply