SQL Log File Corruption and Possible Recovery Methods

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 a data definition language, data manipulation language, and data control language. the sql database comprises of following three files:

1. primary database files : the primary database file is the main database file (mdf), which points to another file in the database and hence, every database has one primary data file. the file is in the extension of .mdf.
2. secondary database files : when the data of a database exceeds, then 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
3. 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 the .ldf file extension.

log files (also known as transaction logs) 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.

the log files of the sql server gets corrupted, i.e. while we are performing some action on sql the 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 the computer system, many viruses can infect and damage the log files and makes them inaccessible.
• terminating system abnormally : if the system/application quits abnormally, 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 or enhanced, it may lead to corruption in log files.
• storage size issue : the biggest reason behind the corruption of log files is the storage size. if the data exceeds the limit of ldf, corruption is likely to occur.

errors that occurs due to log file corruption

error message 1 : startlogfiles: this error exists when the log files are unable to start because the system could not find the file specified. 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 : the corrupted server of the sql database leads to the corruption of the 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 serve, an error message displays with a message "could not open new database [name of database]." create database is aborted.

error message 6 : when the log database attempts 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 files 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.

about the author

andrew jackson is a sql dba and sql server blogger too. he likes to share about sql server and the problems related to it as well as their solution and also he do handle database related user queries, server or database maintenance, database management etc.