October 12, 2008 at 5:46 pm
Hi,
Iam trying to move log files to different drive. In this process Iam trying bring the database offline by executing
ALTER DATABASE database_name SET OFFLINE but keeps on running ,and giving no error message. So should I can do to bring database offline????? and aslo tried ALTER DATABASE database_name SET single_user it also running for ever???
could you plz me in this?
October 12, 2008 at 7:20 pm
Are there people connected to the database?
If you want to move the files, detach the database, be sure you kill the users from it, and then attach it from the new location.
October 12, 2008 at 7:33 pm
Hi Steve,
there are no users connected to that database. When I ran sp_who2 it showing the logins are in sleeping mode. and when Iam trying to put the database in single_user mode I am getting the following error
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'dbname'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
What this error mean?
October 13, 2008 at 6:24 am
Are all logins you see with sp_who2 your own? If not, other users are accessing the database. Kill those connections and try again.
October 13, 2008 at 8:18 am
To alter a database there must be no connections to the DB, sleeping or otherwise. You can either kill the connections or you can use the rollback immediate option on the alter database. Make sure that you are not connected to the database you're trying to alter.
ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE
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
October 13, 2008 at 8:24 am
sleeping connections are still connected.
No one should show that db name in sp_who2. If they do, you need to remove them. That includes you connected with SSMS.
April 28, 2009 at 10:49 pm
I did a sp_who2. Found the culprit. Asked them to get out. And then the database went offline.
December 11, 2010 at 4:56 am
I had this problem too when trying to take a database offline. See here for my full solution walkthrough...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply