Suspect Database SQL 2000...

Na-experience nyo na ba na magkaroon ng suspect database sa SQL Server 2000? Two days ago, I had this problem. How did I fix it? Ganito po:

--1st Step: Open kayo ng Query Analyzer and then allow nyo yung direct update sa system tables.

--Query:

Use Master

Go

sp_configure 'allow updates', 1

reconfigure with override

Go

--2nd Step: Please take note of the status value na nasa sysdatabases.

--Query:

select * from sysdatabases where name = 'YourSuspectDB'

-- where name should be the database name of your suspect database

-- normally ang status ay 16, meaning allow torn page detection

--3rd Step: Put the suspect database in emergency mode (32768).

--Query:

update sysdatabases set status = 32768 where name = 'YourSuspectDB'

-- where name should be the database name of your suspect database

--4th Step: Put the database in single user mode. You don't want anybody tinkering with the

--database while fixing it.

--Query:

sp_dboption 'YourSuspectDB', 'single user', 'true'

--5th Step: Punta kayo sa folder or directory kung saan naka-store yung .LDF ng suspect database --and then rename nyo yung transaction log filename.

--Query:

--NOTE: Gumamit ako ng undocumented command for rebuilding the log.

--parameters are: databasename, filename

DBCC rebuild_log('YourSuspectDB','E:\SQL_Logs\YourSuspectDB_Log.LDF')

--6th Step: Check your database for inconsistencies.

--Query:

DBCC checkdb('YourSuspectDB')

--7th Step: Update nyo yung database status from emergency to torn page detection.

--Query:

update sysdatabases set status = 16 where name = 'YourSuspectDB'

--8th Step: Reset the allow updates back to 0.

sp_configure 'allow updates', 0

reconfigure with override

Go

So far, I've tried these steps on SQL Server 2000 only. Also, this was my last resort since sa test server lang naman nangyari ito at wala akong backup.