July 5, 2011 at 5:07 pm
Hi Everyone,
I tries to take a database offline and move the files to another location. I got an error message: Set offilinefailed for Database 'XXXX'
Additional information:
An exception occurred while executing a Transaction - SQL statement or batch
Alter Databased failed because a lock could not be placed on database 'XXXX', try again later.
Alter database statement failed (microsoft SQL Server, Error 5061)
What should I do?
Thank you
Yan
July 6, 2011 at 1:39 am
A database cannot be taken offline if there are still open connections to it. Either kill all connections to that DB first or use the ROLLBACK IMMEDIATE clause of ALTER DATABASE that will do it for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2011 at 7:43 am
Thank you for the advice. I actually made the database in single user mode and tried to take it off line, but still, it said "Database is laready open and can only have one user at a time. (Microsoft SQL Server, Error 924).
The server is not in local, but in another city. I remotely login to the server, do you think the server counts this a user?
By the way, Login to the Managemenet Studio counts as a one user, if I run Alter Database....set..offiline from the Query Anayser, it should counts as an another user, right?
So, how to open the Query Aanyser without going to SSMC?
Thank you
Yan
July 6, 2011 at 7:48 am
Same thing. To make a DB single user there much be no other users in it.
Forget about messing with single user, make sure your connection is not using that DB (USE master) then use the WITH ROLLBACK IMMEDIATE option of the ALTER DATABASE to take it offline.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2011 at 8:02 am
Thank you! I will surely try it.
One more thing, yesterday, I used SSMS, right click the Database,went to Tasks and used: "Take Offline" function. Do you think this function can give trouble? What is the exactly this "Take Offline" doing? Just Alter Database ...Set Offline?
Yan
July 6, 2011 at 8:25 am
Yup. SSMS just runs commands against SQL. Exactly the same command you can write and run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply