October 10, 2007 at 9:22 am
Hello all.
I am using SQL 2000 running on Win2K3.
I needed to move a database because I was running out of space. Using EM I detached the db. Then I moved the .mdf and .ldf file to the new drive. I then reattached the files and database is now read only.
I tried unchecking the read only attribute but it gives an error that the database in use and no changes can be made. I tried then to detach the database but now the same thing happens where it says cannot detach the db because it is in use. This happens even after it gives the options to clear the connections before you detach.
BTW, this is the db that McAfee EPO uses.
Does anyone have advise on how to proceed? I need to get this db back to read write.
October 10, 2007 at 9:26 am
Why don't you trying restarting the SQL Server Service unless you are running some realtime production databases on the server.
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 9:31 am
Thanks for the reply. It is a production server so i can't restart the service right now.
October 10, 2007 at 10:04 am
Someone is reading the db. I'd remove all connections from the db and then you can make the change.
You might need to "pause" the service, kill connections, then make the change and move the server back to started. It's an interruption, but if you move quickly it won't last long.
October 10, 2007 at 10:58 am
Thanks.
When I kill the connection to that database the status is KILLED/ROLLBACK and still won't allow me to change the properties.
I read in another forum they had to reboot the SQL server to clear it. I'm hoping to find another way but I may just have to reboot tonight.
October 10, 2007 at 11:58 am
If the process you killed is a long running command .. say a large insert .. then when it is killed it does a rollback. If you do another kill statement in query analyzer it will give you a % remaining on the rollback. I've had rollbacks take literally hours before but hopefuly it will finish up and you can take care of your problem. Not sure how it can do a rollback with it read only though.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply