Database in Suspect Mode – SQL Server

What to do when the database is corrupt.
1. Don’t panic
2. Don’t detach the database
3. Don’t restart SQL
4. Don’t just run repair.
5. Run an integrity check
Suspect Mode Recovery for SQL Server 2000
Following are the steps to recover database from Suspect Mode

Solution 1
USE Master
GO
– Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
– Update the database status
UPDATE master.dbo.sysdatabases
SET Status = -32768
WHERE [Name] = ‘YourDatabaseName’
GO
=> Above command will Change database status to Emergency mode, so that database is now made accessible.
=>Run DBCC CHECKDB command to verify database consistency.
=> If DBCC CHECKDB fails with error, depending on the level of corruption and with client permission we can try below commands:
DBCC CHECKDB WITH REPAIR_REBUILD
DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS
– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
– Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
Solution 2
1.Stop SQL Server services, rename the physical file of transaction log which is corrupt. Restart SQL Services back.
2.Execute these commands one by one:
–  Allow updates in system tables
 EXEC sp_configure ‘Allow updates’, ’1′ Reconfigue with override
–Bring the corrupt db in Emergency mode
Update Sysdatabases
Set status = status | 32768
Where dbid = <dbid>
–Take the db in single user mode
EXEC sp_dboption ‘database name’, ‘Single User’, ‘TRUE’
–Rebuild the log file
DBCC REBUILD_LOG (‘db name’, ‘complete log file path with the original name’)
–  You will see a message when the log file is rebuilt. Do run a Checkdb afterwards
DBCC CHECKDB (‘db name’)
–Review the output of the Checkdb. Couple of lines at the bottom would be of your interest. If it still found corruption, it would suggest repair_rebuild, repair_allow_data_loss etc commands to run Checkdb with, again. This should fix the corruption which still exists. Run them (Again, at your own risk as they may incur data loss)
 – DBCC CHECKDB (‘db name’, repair option suggested by checkdb)
 
Suspect Mode recovery for SQL Server 2005/2008
Follow these steps to recover the database from Suspect Mode
Workaround
When the database is in SUSPECT mode, you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.
You can run the following SQL query to get the database to the EMERGENCY mode.
ALTER DATABASE  dbName  SET  EMERGENCY
=> After that, you set the database to the single-user mode. Single user mode allows you to recover the damaged database.(NOT TRUE)
ALTER DATABASE  dbName   SET SINGLE_USER
Then you can run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify “REPAIR_ALLOW_DATA_LOSS” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.
DBCC CHECKDB (‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)
If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:
ALTER DATABASE  dbName  SET MULTI_USER
Recommendations
Using any DATA LOSS repair options can lead to other problems. This is not a recommended way to recover the database. The database should be restored from a backup made prior to the corruption, rather than repaired.
DBCC CheckDB command should be running fine on working databases at regular intervals to check for errors.
Query to rebuild log file in SQL 2005/SQL 2008
————————————————–
alter database mydatabase rebuild log on
(Name=mydatabase_log,filename=’mydatabase_log.ldf’)
—-
in case there is an allocation error mostly due to system objects…
——–General Info…
DBCC checkdb on a database with the no_infomsgs returns the
following result:
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:3365824). The PageId in the page header = (0:0).
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:3365824) with latch type SH. sysobjects failed..
The database that you ran DBCC CHECKDB on has a corrupt sysobjects table -
You cannot proceed with Recover option.
You need to do several things:
1) investigate why this happened. It’s almost certainly a hardware problem.
You should look in the SQL Server errorlog and the NT event log for clues.
2) you need to restore this database from your backups. Running repair will
not work and will fail with the same message repeatedly.