RECOVERING A SQL SERVER 2000 DATABASE FROM A SUSPECT MODE

RECOVERING A SQL SERVER 2000 DATABASE FROM A SUSPECT MODE

Posted by Admin Labels: newupdate, SQL 2000 Suspect, sql error, usefulpost

Error:

·         Database Suspect due to Not enough space.

·         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.

Solution:

Here i take database name as "DBName"

·         First we have to find out physical location of ldf file by using T-SQL query

Select * from sysaltfiles                                                                                

·         Database physical location path like as  D:\MSSQL\Data\DBName_log.LDF

·         Stop the SQL Server Services on the server using services.msc/SQL Server Service Manager.

·         Rename Physical database log file name. i.e DBName_log1.LDF

·         Execute Following query from query Analyzer

EXEC sp_configure 'Allow updates', '1'
Reconfigure with override

·         Set the database named DB_log to Emergency mode by executing the following query 

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

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

·         Bring the database named DBName to Single User mode by using the below query

EXEC sp_dboption 'DBName', 'Single User','TRUE'                                                     

·           Rebuild the Transactional Log file of the database using the below 

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

·          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 OnlyRestricted Access option.