Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-08 : 07:23:37
|
| Sujoy writes "Hi:I'm using SQLServer 2000. I have a database marked suspect. I tried the following:sp_configure 'allow updates', 1goreconfigure with override UPDATE SYSDATABASES SET STATUS=status & ~256 WHERE NAME='sesame'then I changed it to the emergency mode and then try to recycle the server. I thought it would help recover the database but it doesn't. Am I wrong ?Even after I recycle the server, the database is still offline. Is there a way by which I can bring the database online and then reload the data?ThanksSujoy Paul" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-08 : 11:47:19
|
| did you tru to detattch it and reattach it?How's master looking?What's in your error log?Brett8-) |
 |
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2003-07-08 : 11:52:58
|
| If you detach it, you may not be able to attach it back. Did you try resetting the suspect status with sp_resetstatus |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-08 : 12:23:52
|
| Yes you will need to reset the status of the database by using sp_resetstatus and not by updating a system table. You also need to figure out what went wrong. Check the Event Log to see if there are any server problems, also check SQL Server's Error Log. You should also run DBCC CHECKDB on your database to determine if it is corrupt.Tara |
|
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-07-11 : 10:58:04
|
| how about something like............use mastergosp_configure "allow updates", 1reconfigure with overridegoupdate sysdatabasesset status = status - 256where name = "database name"and status & 256 = 256gosp_configure "allow updates", 0reconfiguregocheckpointgoshutdowngo====Paul |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-12 : 08:06:25
|
| Ummmmm...really shouldn't update the system table directly. Even though sp_resetstatus does the same thing, it is expressly designed to fix the suspect status and includes all of the necessary tests and checks to ensure it is updated CORRECTLY. There's no point in doing a back door update when the front door is open and workable.I recently had a suspect database the sp_resetstatus didn't quite fix, fortunately I had a backup that I ended up restoring as a new database. You should prepare yourself for a similar possibility, and if you don't have a backup you can restore...better start making them NOW. There is no substitute. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-12 : 15:01:38
|
update master..sysdatabases SET status = status ^ 256 where name = 'mydbname'This just resets the suspend flag to allow another attempt at recovery - never worked for me.update master..sysdatabases SET status = 32768 where name = 'mydbname'Will set to emergency mode - and will allow access to the database immediately, you don't need to restart the server.Afraid I always do it like this Rob.Whatever happens consider this database as dead - the best you can can hope for is to transfer data into another database.As Rob said your backup stratgey should mean that you should only ever need to do this on databases not under your control .==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Database marked suspect