This is the last article, but not the least one, in the SQL Server Transaction Log series. In this series of articles (see the TOC below), we described the Transaction Log concept from four different aspects.

In the first articles group, we described the main concept of the SQL Server Transaction, dived deeply the internal structure of the SQL Server Transaction Log, and the vital role that the Transaction Log plays in keeping the database in a consistent state and recovering the corrupted database or mistakenly modified table to a specific point in time.

After that, we went through the three recovery model types, Full, Simple and Bulk-Logged, that controls how the transactions are written to the SQL Transaction Log file, and the relationship between the SQL Server Transaction Log and the different types of high availability and disaster recovery solutions.

After building a good understanding background about the SQL Transaction Log, we discussed how to manage and monitor the SQL Server Transaction Log file growth, the different operations that can be performed on the Transaction Log, such as the log backup, shrink and truncate operations and finally the list of best practices that should be performed by the database administrators in order to keep the SQL Transaction Log in healthy state.

Finally, we discussed how to take advantages from the logs that are automatically written to the Transaction Log in Undoing or Redoing a specific data modification process. In this article, we will see how to rebuild a SQL Server database that has a corrupted or deleted SQL Server Transaction Log file.

Issue definition

When the SQL Server service started, the SQL Server Engine will read the whole Transaction Log file and perform the recovery process, that includes both the Redo and Undo phase. If the reading process or the recovery process fails, the database will not be brought online and will be marked as Suspect or Recovery Pending, based on the failure stage.

The Transaction Log file corruption can be caused due to multiple reasons, include:

• The system terminated abnormally without proper shutdown for the databases
• Hardware or configuration issue occurred with the I/O subsystem that is used to host the system and user databases files
• The system got affected by a virus, malicious software or malware attack that damaged the files or make it inaccessible
• The Transaction Log file ran out of free space and exceeds the configured maximum file size

Troubleshooting

If you are not able to bring the database online as it stuck in SUSPECT or Recovery Pending state , the first action you need to perform is reviewing the SQL Server Error Logs and the Windows Application and System event logs on the SQL Server that is hosting this database. If any hardware issue detected, contact the system administrator or the hardware vendor to fix the issue for you. If the issue is caused due to a Transaction Log file corruption, continue reading this article to know how to fix that issue.

There is a number of errors you may find that indicates an issue with the SQL Server Transaction Log file, such as:

• A file activation error occurred. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\XXX.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation
• SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x186ba635; actual: 0x186b2635). It occurred during a read of page (2:0) in database ID 22 at offset 0000000000000000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\XXX.ldf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online
• The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure

The best and the safest option to fix the database Transaction Log file corruption issue is restoring the database from the latest backup chain, that includes restoring the Full backup, The Differential backup, and all Transaction Log backup to the last healthy point in time before the corruption occurred.

But what if this option is not applicable, due to having no proper backup strategy configured or some of the backup files in the current backup chain were lost? In this case, we cannot accept the data loss that is resulted from restoring the last Full backup file or half of the backup chain before reaching the lost backup file, as the database contains critical data. The last applicable option, that deserves trying is rebuilding the Transaction Log file, as we will show in the next section, tolerating the lost of recovering, undo and redo, the transactions that were located in the original Transaction Log file.

Resolution

In order to rebuild the corrupted SQL Server Transaction Log file, we should put the database in the Emergency state with Single User Mode, using the command below:

In this way we ensure that the database will be brought up without the Transaction Log file, allowing us to run the proper commands that are required to fix the file corruption issue:

After that, we will try to execute the DBBC CHECKDB command using the REPAIR_ALLOW_DATA_LOSS option, in order to check the database for any inconsistency error and apply some special repairs to fix the Transaction Log corruption issue, as in the T-SQL script below:

Unfortunately, the corruption of the SQL Transaction Log in our database cannot be fixed using the DBCC CHECKDB command and requires further troubleshooting to make it fixed. The error message is received from the DBCC CHECKDB command in our case will be like:

If you reach this step and the SQL Server Transaction Log file still corrupted, I recommend you NOT to detach the database and try to attach it without referencing the Transaction Log file in order to create a new Transaction Log file.

If you try to detach the corrupted database, as below:

Then try to attach it while removing the reference to the SQL Transaction Log file:

The attach process will fail, showing the error message below:

In order to attach it correctly without using the SQL Server Transaction Log file, renamed the old Transaction Log file:

Then run the CREATE DATABASE command using the FOR ATTACH_REBUILD_LOG command, shown below:

And the database will be attached using the MDF file, with a new SQL Transaction Log file created on that database to replace the corrupted one, as shown below:

Another option to rebuild the corrupted SQL Server Transaction Log file is taking the database offline, as below:

And the database will be marked as Offline, as shown below:

Then change the corrupted SQL Transaction Log file name, as shown below:

Then run the ALTER DATABASE T-SQL command below, using the REBUILD LOG option, and provide the SQL Server Transaction Log file original name in order to rebuild the file again for that database, as in the T-SQL script below:

And the SQL Server Engine will rebuild the Transaction Log file, with a warning message recommending you to run the DBCC CHECKDB command to validate the physical consistency of the database, as shown below:

After that, we will bring the database online, as shown below:

And the database will be brought online under Single User Mode state, shown below:

Now, we will return the database back to the MULTI_USER online mode, using the ALTER DATABASE statement below:

And the database will be in the Online state, as shown from the SQL Server Management Studio below:

As mentioned in the previous warning message, we will run the DBCC CHECKDB command below, in order to check the physical consistency of the database after rebuilding the SQL Transaction Log file:

And the DBCC CHECKDB will show us that there is no consistency issue on that database that needs to be fixed, as in the result message below:

All is fixed now!

Although this method fixed the SQL Server Transaction Log file corruption issue, it may result with losing some data that are written to the Transaction Log but not hardened yet to the underlying disk after a CHECKPOINT process, without knowing what was lost exactly. Nothing will be better than having a proper backup strategy that helps to recover the data in case of any corruption without falling in the risk of any data loss.

We reached the end of the SQL Server Transaction Log articles series. Hope you enjoyed it. Stay tuned to the next articles series ?

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021