Recently one of our Staging Database Server hosted in the United States of America suddenly got Rebooted after which one of the database named ABC hosted on the server went into the SQL Server SUSPECT mode. The database was hosted on a SQL Server 2000 platform with Service Pack 3 on a Windows Server 2003 R2 Enterprise Edition machine.
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 which will in turn have a very bad impact on the Business Users as they won’t be able to perform their testing. At the same time when the Transactional Log file of the database got corrupted, the Backup Drive B present on the server also got corrupted which meant that I will never have the Latest Full Backup to recover the Data Back. Looking at the entire situation, I decided to implement the below solution to repair the database. Please keep in mind that this solution is the one which shall be used only at the worst stage, i.e. when the Transactional Log File of the database gets corrupted and you do not have any Backups to recover your data back.
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.
This was one such approach that we used to recover the database back since we lost our database backups. Please do let us know in case if there are any other suggestions for the same, any approach which can be more effective and useful. Please feel free to drop your suggestions\queries on singhsatnam84@yahoo.com and I would be glad to assist you.
Regards
Satnam Singh
Like us on FaceBook | Follow us on Twitter
Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook
Thanks satnam singh for sharing. I had an experience with this kind of problem. now i got the actual reason for this issue. keep posting.
Wonderfully written article, Doesnt confuse already scared DBA’s
Thanks for sharing your solution, it worked perfectly 🙂
Thanks for this great write up. I followed the steps and we were able to recovery the database. Thanks again
Indeed very excellent article which solve my problems. thanks million
Thanx a ton .. it helped really and enabled recovery from a major disaster. But what if it shows following message on querying from some of the tables:-
Server: Msg 605, Level 21, State 1, Line 1
Attempt to fetch logical page (1:35448) in database ‘TIPSSPMS’ belongs to object ‘689110312’, not to object ‘Ts_ropd’.
Connection Broken
Great article. Was a lifesaver. Thanks!
Thank you for providing the steps and explaining the reason for each it helped me tremendously.
Thanks a lot!We had the same problem and we managed to fix it thanks to you!
A real life saver!
Great article.It worked like magic.Be blessed.