SQL Server SUSPECT Mode : Recovering a SQL Server 2000 Database

Recover SQL 2000 database from Suspect Mode

In Some times, When Server got rebooted automatically or due to sudden power disturbance the database of SQL 2000 marked as Suspect. Due to a sudden Reboot the Transactional Log file of the database named ABC got corrupted due to which the database went into SUSPECT mode. Since database got marked as SUSPECT it meant that the Application will not be able to connect to the database .

I decided to find the Physical Location of the Transactional Log file of the database named ABC by executing the below T-SQL against the master database on the server.

Select * from sysaltfiles

I got the Physical location of the Transactional Log file of the database named ABC. It was as follows:

D:\MSSQL\Data\ABC.LDF

After getting the Physical Location, I stopped the SQL Server Services on the server. Since it was a Stand Alone machine and not a cluster therefore I stopped the services by selecting the same from services.msc .

I decided to rename the Transactional Log File of the database as ABC_1.ldf. Please note that you need to be an administrator on the server to rename the file.

Allow the SQL Server to perform the system changes by executing the below T-SQL.

EXEC sp_configure ‘Allow updates’, ’1′

Reconfigure with override

Set the database named ABC to Emergency mode by executing the following query against the master database on the server.

UPDATE master.dbo.sysdatabases
SET Status = -32768
WHERE [Name] = ‘ABC’
GO

• Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members ofsysadmin fixed server roles have privileges to access it.

Bring the database named ABC to Single User mode by using the below T-SQL:

EXEC sp_dboption ‘ABC’, ‘Single User’,’TRUE’

The next step would be to Rebuild the Transactional Log file of the database using the below T-SQL.

DBCC REBUILD_LOG (‘ABC’,’D:\MSSQL\Data\ABC.LDF’)

Where ‘D:\MSSQL\Data\ABC.LDF’is the original path and name of the Transactional Log File.

Run DBCC CHECKDB as follows to ensure that the database is free from any sort of corruption:

DBCC CHECKDB (‘ABC’)

If DBCC CHECKDB doesn’t print any error message then it means that we are successful.

Go to Database Properties and Uncheck the DBO User Only Restricted Access option.

Short Note of Query

Step1

Sp_configure 'allow updates', 1
Reconfigure with override 

Step2

update sysdatabases
set status = status | -32768
where name = 'ABC'

Step3

EXEC sp_dboption ‘ABC’, ‘Single User’,’TRUE’DBCC REBUILD_LOG (‘ABC’,’D:\MSSQL\Data\ABC.LDF’)DBCC CHECKDB (‘ABC’)