Error: Transaction log file got damaged or corrupted

by Posted on

Once we had an issue in our environment. We had a maintenance activity on the Database server during a weekend and the server was shutdown for a Storage level snapshot backup. After the backup the server was brought back online. But unfortunately the user database was in suspect mode and could not be accessed. SQL Server Error Log says

“An error occurred while processing the log for database ‘<databasename>’.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”

Solution

Below is what I did to rescue my beloved database.

1. Enabled emergency mode for that database.

ALTER DATABASE set EMERGENCY

2. Then brought the database into single user mode.

ALTER DATABASE set SINGLE_USER

3. Executed DBCC CHECKDB with repair_allow_data_loss.

DBCC CHECKDB (<databasename>,repair_allow_data_loss) with no_infomsgs

I got below warning in Error Log right after I triggered the CHECKDB command.

Warning: The log for database ‘<databasename>’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

And once the CHECKDB completed, The errorlog log reported below message which made me very happy.

EMERGENCY MODE DBCC CHECKDB (<databasename>, repair_allow_data_loss) WITH no_infomsgs executed by SPANSION\admin_sponnamb found 0 errors and repaired 0 errors. Elapsed time: 0 hours 52 minutes 54 seconds.

After the CHECKDB completed, I reverted back the database into multiuser mode with below command.

ALTER DATABASE <databasename> SET MULTI_USER

Now the application was successfully able to access the database and everything was function very well as expected. A full backup has to be triggered to reset LSN numbers and to take transaction log backups.

Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.