question

ramesh 1 avatar image
ramesh 1 asked

sql server 2005 - Suspect database

HI TEAM, I use sql server 2005 standard edition 32 bit, my database went into SUSPECT MODE. please help me
sql-server-2005suspect
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Well, you could try resetting the database to non-suspect mode, but the chances are this won't achieve anything as SQL Server will (probably) put it back into suspect mode. EXEC sp_resetstatus 'DBname' Your only real strategy is to restore from a clean backup. You do have backups, right?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes i have
0 Likes 0 ·
@Thomas - hey, congrats on the 3k!!
0 Likes 0 ·
Thanks, @Fatherjack!
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
Appart from @ThomasRushton method, another way is as follows
-- Take the database into Emergency mode
ALTER DATABASE  [Database Name] SET  EMERGENCY
GO
-- Change into single user mode
ALTER DATABASE  [Database Name] SET SINGLE_USER

-- Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)

-- Change from multiuser
ALTER DATABASE  dbName  SET MULTI_USER


The Recommended way to recover the database is by restoring from a backup made prior to the corruption, rather than repaired. I found a similar question [here ][1] [1]: http://ask.sqlservercentral.com/questions/375/how-to-fix-a-database-marked-as-suspect
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 You can use the DBCC CheckDB with REPAIR_REBUILD to see if the problem can be fixed without data loss. If the corruption is in a non clustered index you may drop it and recreate it. If it is in a clustered index you need to restore a backup.
3 Likes 3 ·
I would strongly caution against using REPAIR_ALLOW_DATA_LOSS as a first step on a suspect database. There are a number of things that should be attempted prior to that and even then, doing the repair that way should be enetered into pretty carefully, with full assurance of backups and all that stuff. Sending them over to Paul Randal's blog is a good start to the possibility of a safe recovery.
3 Likes 3 ·
Agree with Grant here. My first thought when I saw Cyborg's suggestion was "ARRRRGGGHHHH!!" REPAIR_ALLOW_DATA_LOSS does exactly what it says on the tin. Brute force fix of the broken page, even if it means destroying valid information.
1 Like 1 ·
+1 For providing additional info!
0 Likes 0 ·
but william, I clearly mentioned regarding this on my code as a comment("REPAIR_ALLOW_DATA_LOSS can cause some data to be lost!" I mentioned "The Recommended way to recover the database is by restoring from a backup made prior to the corruption, rather than repaired"
0 Likes 0 ·
Show more comments
Fatherjack avatar image
Fatherjack answered
If there isnt too big a gap since your last backup then I would go for the restore option as first call. **Dont** restore over the currently suspect database, restore it as a different name and check its all OK. If that restores in good order then you know that you can start to explore with DBCC CHECKDB safe in the knowledge that you have a good backup at a known point in time. You may get some data from the suspect database and be able to get it online successfully, however, you may also fry it completely and need the restored version. If you have to use the restored database it can easily be renamed or restored again but this time overwriting the broken copy.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
Restoring from the Backup DB might be easy but it will be difficult to restore if you haven't got a recent backup. For example ..you are backing up DB for every 4 hours however if your db is become SUSPECT after 3 hours the backup run and users already made changes in your DB. However is that easy to detach the DB and reattach again to re-slove the issue rather then using Repair_allow_data_loss?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A DB in suspect state cannot be (directly) detached. It needs to be put into emergency mode to do this, but it is not something I would suggest you do. It is much better to try what everyone else has said. As for the data loss after 3 hours for a 4 hour backup window - this is something that has been given the nod by management. They have said they can live with up to 4 hours loss by mandating a 4 hour backup window. Maybe that would be reviewed after the first data loss though!
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
If dealing with production, I recommend Fatherjack's approach. It is generally the safest and closest to gaurunteed success without dataloss you are going to get. I have in test and development environments had some success with simply detaching and reattaching the database file, depending of course on what made it go into suspect mode. Again, I would absolutely not recommend this for production, but if it is a test environment or similar where you care more about not using a lot of DBA time then you do about gaurunteed success this is fast.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.