Troubleshoot Suspect Database

Sometimes we encounter problem that the database is in suspect mode and cannot be recovered if we do not have the latest backup of the database. This may take place when the database is not closed properly before SQL Server shutdown. Sometimes the DBA try to detach the suspect database and try to attach the data and log file of the suspect database. It gives following error:

“The log scan number (number:number:number) passed to log scan in database 'database_name' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.”

Sometimes we also try to repair the Suspect database using run the DBCC CHECKDB command with the REPAIR_REBUILD, but it fails with the following error:

“Database 'databasename' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server error log for more information.”

Following is the solution to resolve this issue:

If the DBA has detached the database try following steps:
1.Detach the suspect database.
2.Rename the existing “.ldf” to “_old.ldf” file
3.Use following attach command to attach the database which will create new log file.

CREATE DATABASE database_name
ON [ ,...n ]
FOR ATTACH_REBUILD_LOG

If DBA has not detached the database then

1.Set database to emergency mode by using following command

ALTER DATABASE SET EMERGENCY

Use Master
GO
ALTER DATABASE SET SINGLE_USER
GO
DBCC CHECKDB(database_name, repair_allow_data_loss)

ALTER DATABASE SET MULTI_USER

ALTER DATABASE SET ONLINE

The database will be back online.