[toc]
Introduction
Structured Query Language (commonly known as SQL) is a programming language used for managing data held in relational database management system (RDMS) consisting of data definition language, data manipulation language and a data control language. The SQL database comprises of following three files:
- Primary Database Files: Primary database file is the main database file (MDF) which points to another file in database and hence every database has one primary data file. The file is in the extension of .mdf
- Secondary Database Files: When the data of a database exceeds than a secondary database file is created which stores the data that exceeds MDF limit. Hence, multiple secondary data files can be created for a single database. The file is in the extension of .ndf
- Log Files: These files maintain a log of all the transactions done in SQL Server Database so that that information can be later used to recover the database. There must exist one log file for each database and it is possible that more than one log file can be created for a single database. The file is in .ldf file extension.
Log files (also known as transaction log) consist of actions executed on the database for database management to guarantee ACID properties over crashes or hardware failure. It is a file listing changes to the database, stored in a stable storage format
Problem
The log files of the SQL Server gets corrupted i.e. while the time we are performing some action upon SQL database, there exist some errors in between the process and due to which there is an interrupt in process.
Causes of Log File Corruption in SQL Server
- Viruses or other Malicious Software- In computer system, many viruses can infect and damage the log files and makes them inaccessible.
- Terminating System Abnormally- If system/application is quit abnormally, then files are prone to be corrupted or damaged.
- Input Output Configuration- The I/O subsystem is a vital component of the database used to store system and user databases. Hence if the configuration is disturbed on enhanced that it may lead to corruption in log files.
- Storage Size Issue- The biggest reason behind the corruption of log files is the storage size. In case the data exceeds the limit of LDF, corruption is likely to occur.
Errors which Occurs Due to Log File Corruption
Error Message 1: StartLogFiles: The error exists when the log files are unable to start because the system could not find the file specified. So try to diagnose and correct the operating system error, and retry the operation.
Error Message 2: File activation failure. The error occurs due to error in file on location ‘C:\ProgramFiles\MSSQLServer\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG’.
Error Message 3: The error message displays that the transaction log has been deleted or lost due to a hardware failure of a system or any other reason. The log files cannot be redeveloped because users perform open transactions on file.
Error Message 4: Corrupted server of SQL database leads to corruption in backup of log files.
Error Message 5: When database log is corrupted and a user is attempting to attach the log file to the new server then an error message displays with a message that ‘Could not open new database (Name of Database). CREATE DATABASE is aborted.
Error Message 6: When the log database is attempted to attach but it gives an error while performing the attachment. The error displays one of the two number i.e. 9004 or 9001 notifying that you have to create a backup or it is necessary to rebuild the log.
How to Recover Corrupted Log File in SQL Server?
In order to repair a corrupt LDF file, use the WITH TABLOCK option for DBCC CHECKDB. It will recover the data from a corrupted LDF file that has been corrupted or damaged due to some reason such as logical corruption.
dbcc-checkdb
Conclusion
Through the above solution, we can recover SQL log file but not sure that the above procedure will recover complete log file data. However, there exists SQL Server transaction log recovery tool through which you can restore database transaction log.