Error corrupted transaction log file

How to Fix A Corrupted SQL Server Transaction Log File

A corrupted transaction log file (.ldf) may result from any errors, such as unexpected system shutdown, hard drive problems, virus attack, etc. When you happen to meet this issue, you can try methods here to fix log file corruption in SQL Server. If you still fail and can’t access the MDF file, you can try EaseUS MS SQL Recovery to help you read and export data in MDF, so that the SQL can work normally.

How do I fix an SQL Server log file that is corrupted?

«This morning, when I tried to open my SQL database, I was informed that one of the transaction log files had corrupted due to some unknown reasons. A simple restart didn’t solve the problem. Now, the database is inaccessible, and I can’t read the database content. As a new DBA, I really don’t know many other solutions. Can anyone show me effective ways to fix log file corruption in SQL Server? Thanks in advance.»

The transaction log file, also known as log database file (.ldf), keeps a record of everything down to the SQL database for rollback purposes. There is one or more log files for each database, which are used to maintain the log of all the transactions. Along with MDF and NDF, they are major parts of the database, directly affecting the smooth running of the SQL server. If any of them has a problem, like a log file corruption, the SQL server will stop working correctly.

In our last post, we have talked about how to repair a corrupted MDF/NDF file. Here, we will continue to explore how to fix a corrupted log file that can cause you to lose access to the database files. Unlike MDF database file corruption that can be repaired with third-party software, log file damage can only be fixed manually. Now, let’s start by understanding the causes of this issue.

Causes of Corrupted Log File in SQL Server

The SQL Server log file corruption can be attributed to multiple reasons, including what listed below.

• Hard drive issue: Hard drive damage can be associated with log file corruption as all the database files and components are stored in the hard drive.
• Log file runs out of space: If the log file exceeds its maximum size, it can get corrupted and bring a lot of problems.
• Abnormal system shutdown: When the system closes abruptly, it can easily lead to corruption error.
• Virus attack: Virus or malware can also infect the log file and cause corruption.
• Input-Output configuration issue: If the configuration is changed or interrupted, then corruption in log file arises.

A Reliable Solution to Fix SQL Log File Corruption

Now that you’ve understood why the problem happened, next, try to deal with it with ways given below.

First, check for the hardware issue that may have caused the corruption of log file. You need to analyze the event logs of Windows system and application. If you find any hardware problem, fix it immediately and see whether the corruption issue has been resolved or not. If not, apply the following methods to repair the damaged log file.

1. Create a full backup of the database (MDF file).

2. Open the SQL Server Enterprise Manager, then delete the «suspect» database. If a prompt shows deletions error, you can restart the database server and try again.

3. In the SQL Server Enterprise Manager, create a new database with the same name (e.g., test), and note that the database name and data file name should be consistent with the original database.

4. Detach the database server.

5. Delete the test_log.ldf of the newly created database, and overwrite the generated test_data.mdf file with the MDF file that needs to be restored.

6. Start the SQL server. You will see that the “test” database is in the «suspect» mode. You cannot do anything with this database at this time.

7. Perform the following SQL syntax.

use master
go
sp_configure ‘allow updates’,1
reconfigure with override
go
update sysdatabases set status=-32768 where dbid=DB_ID(‘dbname’)
dbcc rebuild_log(‘dbname’,’d:zc_post_log.ldf’)
dbcc checkdb(‘dbname’)
sp_dboption ‘dbname’,’dbo use only’,’false’

Note: As you have seen, the above steps are very complicated. You should be technical enough to perform the syntax commands. Otherwise, any error will lead to a more severe issue in the SQL server.

How to Recover MDF from a Corrupted Transaction Log File

If you don’t have the confidence to use this manual method or you’ve tried but failed, you can still access your SQL database with an MDF recovery and repair program. EaseUS MS SQL Recovery is a superior database repair software to help you recover data from MDF file and let the SQL load properly. This mature SQL database recovery tool provides the following services.

• Repairs corrupted SQL database files (MDF/NDF files)
• Recovers deleted records in SQL server
• Repairs damaged SQL server database components — tables, triggers, indexes, keys, rules & stored procedures
• Export SQL database to CSV, HTML, XLS, and MDF formats

Now, download this SQL Server repair tool to fix the corrupted transaction log file.

Step 1. Download and run EaseUS MS SQL Recovery.

Step 2. Click the two dots (Browse button) to choose the target MDF file or click «Search» and select the desired file to repair from the list found below. Then, click «Repair» to start the repairing process.

Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.

Step 4. Click the «Export» button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to «Export to database», you can create a new database or export to an existing database.

Conclusion

SQL Server log file corruption can happen at any time, causing many problems for SQL Server users. And one of the most common problems is the inability to access the database. Fixing a broken log file can be quite complex. You need to do multiple checks, try multiple solutions, and maybe none of them will work. So we strongly recommend that you use an easy workaround to fix the corrupted transaction log file by recovering the MDF file. Then, the SQL will load the database normally, and you can read the data again.

Источник

Error corrupted transaction log file

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have a strange problem in one of the SQL 2012 SP1 database. Sometimes my transaction log file grow unexpectedly than the preallocated space and i had to shrink it back . I usually preallocate space to around 15GB and the autogrow is set to 1000MB. The VLF count is less than 50. Database recovery model is Simple.

After i shrink the file and preallocate space again,everything seems to be normal.But later when the backup starts, SQL server starts generating the below error. The database will remain online and accept connections. Users do not get any errors or disconnected. The logs continue to grow as it cannot issue a checkpoint. We resolve this error by restarting SQL as database goes through crash recovery and comes up clean. Checkdb does not show any data corruption as well. I am not sure why the log gets corrupted after the shrink operation. Can someone please advice.

«The log scan number (10082614:43271:1) passed to log scan in database ‘xyz’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.»

«One or more recovery units belonging to database ‘xyz’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.»

Источник

SQLShack

How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File

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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAXXX.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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAXXX.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:

Источник

A database in SQL Server comprises three types of files: primary data file (.mdf), secondary data file (.ndf), and transaction log file or log database file (.ldf).

The primary and secondary data files are used for storing information about database objects like tables, indexes, triggers, views, etc. On the other hand, log file records all transactions and changes made to the database by each transaction. Corruption in any of these data files or the log file can make the database inaccessible. In this blog, however, we will restrict our discussion on log file corruption and methods to fix a corrupt .ldf file.

Before We Begin

Before discussing methods to fix a SQL database with a corrupt log file, it is important to determine the reasons that led to such an issue in the first place.

What Causes Corruption in a Transaction Log File?

Some possible reasons that may result in log file corruption are as follows:

• Abnormal System Shutdown: The system terminated abnormally, due to power outage or other reasons, without clean shutdown of the database.
• Storage Size Issue: Transaction log file has limited storage space, and exceeding its limit increases chances of corruption.
• Hardware Issue or Faulty Memory: Hardware fault occurred with the I/O subsystem used for hosting the system and database files, or faulty memory.
• Virus Infection: The system hosting SQL server database is affected by a virus attack, resulting in a corrupt log file.

Errors You May Encounter Due To Log File Corruption

Error 1 – Error Message 15105

Operating system error 23 (failed to retrieve text for this error. Reason: 15105) on file “C:Program FilesMicrosoft SQL ServerMSSQL10_50.MYSERVERMSSQLDATAMY_DATABASE.ldf” during CheckLogBlockReadComplete

Error 2 – Failed to Attach Log File

When trying to attach a log file on a new SQL Server, you may receive an error:

“Could not open new database ‘Your_DatabaseName’. CREATE Database is aborted”.

Error 3 – File Activation Failure (Location File Error)

File activation failure. The physical file name ‘C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAXXX.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

FileMgr::StartLogFiles: Operating system error 2 (The system cannot find the file specified.) occurred while creating or opening file ‘E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAxxx.ldf’. Diagnose and correct the operating system error, and retry the operation.

Methods to Repair SQL Server Database with a Corrupt Log File

NOTE: Check the SQL Server error logs and event logs of the Windows system and application hosting the database. If you find any hardware problem, get it fixed and see whether the corruption issue has been resolved or not. If not, apply the following methods to repair the damaged log file. 

Method 1 – Restore Database from a Good Backup

NOTE: If you don’t have a proper backup strategy configured or the backup files are lost, skip to method 2.

The simple and safe approach to fix log file corruption issue is restoring the database from the last healthy, point-in-time, database backup.

Let’s look at the steps to restore SQL Server database from full database backup:

• Open SQL Server Management Studio (SSMS), and connect to SQL Server.
• Expand Databases folder in Object Explorer.
• Right-click the Database, select Restore Database.

• In Restore Database window, under Source for restore section, select From device, and then click the button next to it to specify database file location.

• In Specify backup window, select Backup media type, and then click Add button to insert the backup file location.  

• Select the backup file you need to restore and click OK.
• Again, click OK.

• The backup file will be listed on the Database restore window.
• Before restoring the backup, select Options under Select a page section in the Restore Database window.
• Select one of the checkboxes under Restore Options section. Click OK.

Method 2 – Rebuild Corrupt Transaction Log File

Try rebuilding the corrupt log file to make the database accessible again. To do so, follow these steps:

• Put the database in EMERGENCY MODE by using the following command:

ALTER DATABASE  SET EMERGENCY, SINGLE_USER
GO

Running the above command will help bring the database up without a transaction log file.

• Once the database is up and set it to SINGLE_USER mode, run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to repair the database with a corrupt log file:

NOTE: Before proceeding with the repair process, make sure to create backup copies of your database. This is because the REPAIR_ALLOW_DATA_LOSS option involves data loss risk.

DBCC CHECKDB ('TestTRNLogCorrupt', REPAIR_ALLOW_DATA_LOSS)

If corruption is still there then, use the following command to rebuild the log file. Bring the database in Offline mode and change the name of the corrupted log file associated with it. Now, run the following command:

ALTER DATABASE [original_log_file_name] REBUILD LOG ON (NAME= logicalname, FILENAME='C:Program FilesMicrosoft SQL ServerMSSQLn.MSSQLSERVERMSSQLDATAfile_name.ldf')

Method 3 – Use Stellar Repair for MS SQL Software

If you still cannot access your database, using SQL database repair tool may help. The software repairs the .mdf file and creates a new log (.ldf) file.

Steps to use Stellar Repair for MS SQL Software

• Download, install and launch Stellar Repair for MS SQL software.

• The Instruction window is displayed. Follow the instructions and click OK.

• In Select Database window, click Browse to select the corrupt MDF file you want to repair.

NOTE: If you are not aware of the MDF file location, click ‘Search’ to find and select the file.

• Once the file is selected, click Repair to initiate the repair process.
• The software provides a preview of the repaired database file in a tree-view list on the left panel of Preview window.

• Select all or specific database objects you want to recover, and then click Save on Home menu.
• In Save As window, select MDF under Save As section. Next, choose whether you want to save the repaired database file in ‘New Database’ or ‘Live Database’.

• Fill in the required details under Connect to Server section, and then click Save.

Conclusion

SQL Server database transaction log file becomes corrupt due to several reasons, including unclean shutdown of the database, hardware fault, large-sized LDF file, and virus attack. A corrupt log file can make the database inaccessible.

You can try restoring the database from the last known good backup. But, keep in mind, restoring database from backup may cause data loss depending on the recovery strategy in place.

If the backup is not available or has failed, you can try rebuilding the corrupt log file using DBCC CHECKDB command. But this command has certain limitations. It may fail to fix a severely corrupted SQL Server database and returns an error. For instance, you may receive the following error when trying to repair a severely corrupt database file:

Also, running DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option results in data loss.

If none of the methods works, use Stellar Repair for SQL software to repair corrupt log file and fix SQL database issues, without data loss risk.

FAQ

Q. I have been receiving an error ‘Transaction log backup’ failed. Can I use DBCC CHECKDB command to check for issues with transaction log backup?

Answer. No, DBCC CHECKDB does not check the transaction log file.

Q. Does taking a full backup reset the log backup?

Answer. No, doing a full backup does not reset the log backup.

Q. What is the difference between ‘DBCC CHECKDB command used with REPAIR_ALLOW_DATA_LOSS option’ and ‘ALTER DATABASE REBUILD LOG ON command’?

Answer: The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option checks the database for any inconsistency error. The command first attempts to use the log file to recover from any database inconsistencies. Secondly, if the log is missing it helps rebuild the transaction log file.

The ALTER DATABASE REBUILD LOG command won’t work if there were open transactions (not written to disk) when the log file turns inaccessible.

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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAXXX.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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAXXX.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

How do I fix an SQL Server log file that is corrupted?  

«This morning, when I tried to open my SQL database, I was informed that one of the transaction log files had corrupted due to some unknown reasons. A simple restart didn’t solve the problem. Now, the database is inaccessible, and I can’t read the database content. As a new DBA, I really don’t know many other solutions. Can anyone show me effective ways to fix log file corruption in SQL Server? Thanks in advance.»

The transaction log file, also known as log database file (.ldf), keeps a record of everything down to the SQL database for rollback purposes. There is one or more log files for each database, which are used to maintain the log of all the transactions. Along with MDF and NDF, they are major parts of the database, directly affecting the smooth running of the SQL server. If any of them has a problem, like a log file corruption, the SQL server will stop working correctly.  

In our last post, we have talked about how to repair a corrupted MDF/NDF file. Here, we will continue to explore how to fix a corrupted log file that can cause you to lose access to the database files. Unlike MDF database file corruption that can be repaired with third-party software, log file damage can only be fixed manually. Now, let’s start by understanding the causes of this issue.

Causes of Corrupted Log File in SQL Server  

The SQL Server log file corruption can be attributed to multiple reasons, including what listed below.

• Hard drive issue: Hard drive damage can be associated with log file corruption as all the database files and components are stored in the hard drive.
• Log file runs out of space: If the log file exceeds its maximum size, it can get corrupted and bring a lot of problems.
• Abnormal system shutdown: When the system closes abruptly, it can easily lead to corruption error.
• Virus attack: Virus or malware can also infect the log file and cause corruption.
• Input-Output configuration issue: If the configuration is changed or interrupted, then corruption in log file arises.

A Reliable Solution to Fix SQL Log File Corruption

Now that you’ve understood why the problem happened, next, try to deal with it with ways given below.

First, check for the hardware issue that may have caused the corruption of log file. You need to analyze the event logs of Windows system and application. If you find any hardware problem, fix it immediately and see whether the corruption issue has been resolved or not. If not, apply the following methods to repair the damaged log file.

1. Create a full backup of the database (MDF file).

2. Open the SQL Server Enterprise Manager, then delete the «suspect» database. If a prompt shows deletions error, you can restart the database server and try again.

3. In the SQL Server Enterprise Manager, create a new database with the same name (e.g., test), and note that the database name and data file name should be consistent with the original database.

4. Detach the database server.

5. Delete the test_log.ldf of the newly created database, and overwrite the generated test_data.mdf file with the MDF file that needs to be restored.

6. Start the SQL server. You will see that the “test” database is in the «suspect» mode. You cannot do anything with this database at this time. 

7. Perform the following SQL syntax.

use master
go
sp_configure ‘allow updates’,1
reconfigure with override
go
update sysdatabases set status=-32768 where dbid=DB_ID(‘dbname’)
dbcc rebuild_log(‘dbname’,’d:zc_post_log.ldf’)
dbcc checkdb(‘dbname’)
sp_dboption ‘dbname’,’dbo use only’,’false’

Note: As you have seen, the above steps are very complicated. You should be technical enough to perform the syntax commands. Otherwise, any error will lead to a more severe issue in the SQL server.

How to Recover MDF from a Corrupted Transaction Log File

If you don’t have the confidence to use this manual method or you’ve tried but failed, you can still access your SQL database with an MDF recovery and repair program. EaseUS MS SQL Recovery is a superior database repair software to help you recover data from MDF file and let the SQL load properly. This mature SQL database recovery tool provides the following services.

• Repairs corrupted SQL database files (MDF/NDF files)
• Recovers deleted records in SQL server
• Repairs damaged SQL server database components — tables, triggers, indexes, keys, rules & stored procedures
• Export SQL database to CSV, HTML, XLS, and MDF formats

Now, download this SQL Server repair tool to fix the corrupted transaction log file.

Step 1. Download and run EaseUS MS SQL Recovery.

Step 2. Click the two dots (Browse button) to choose the target MDF file or click «Search» and select the desired file to repair from the list found below. Then, click «Repair» to start the repairing process. 

Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.

Step 4. Click the «Export» button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to «Export to database», you can create a new database or export to an existing database. 

Conclusion

SQL Server log file corruption can happen at any time, causing many problems for SQL Server users. And one of the most common problems is the inability to access the database. Fixing a broken log file can be quite complex. You need to do multiple checks, try multiple solutions, and maybe none of them will work. So we strongly recommend that you use an easy workaround to fix the corrupted transaction log file by recovering the MDF file. Then, the SQL will load the database normally, and you can read the data again.