February 2, 2007 at 7:23 am
i have multiple databases our my db server. One DB is continously showing its status in Recovery when i try to detach it or bring it offline it wont do that. any solutions please?
February 2, 2007 at 9:38 am
update master..sysdatabases SET status = 512 where name = 'mydbname'
shoul update to set database to offline. their are other status you can update to set to not recovered for example. look in books online sysdatabases for status numbers. first try force offline and detach reattach.
February 2, 2007 at 9:45 am
Forgot to mention setting to emergency can sometimes get you access to data.
February 2, 2007 at 9:51 am
Please tell me how ?
February 2, 2007 at 9:59 am
when i issue following commad
Update sysdatabases set status = 512 where name='MYDB'
I get following error:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
February 2, 2007 at 10:09 am
when i searched i got following link]
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1
It says that is sql server 2005
"direct updates to the system tables are not supported."
someone told me that i have to restart sql server 2005 in single user mode and then Bring that DB offline then Detach it and then exit from single user mode bring my sql server to normal mode and then re-attach Database.
What should i do ?
February 2, 2007 at 1:03 pm
What cuased this db to go into recovery mode?
You can use ALTER DATABSE with EMERGENCY option... check BOL for syntax..
http://msdn2.microsoft.com/en-US/library/ms174269.aspx
Note: Before setting it to emegency mode check why it is in recovery mode...Did you sql error log...
may db is really recovering...
when ever you start the sql server all dbs have to recover... they means...dbs have to roll forward committed transactions and roll back uncommitted transactions...
MohammedU
Microsoft SQL Server MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply