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.

Another option to rebuild the corrupted SQL Server Transaction Log file is taking the database offline, as 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 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.