Database Corruption Part 5 :: Dealing with Log File Corruption
Hi Friends,
I am back again after a long time. Last month was the end of yet another financial year, and all of us were very busy with the processes involved with each year-end. Now that all those are completed, I could find some time for my next blog post on database corruption.
Here, I will be targeting issues related to corruption in the database log files. As we all know, the Transaction Log File(s) of a database (*.ldf files) are essential for maintaining transactional consistency in the databases.
In my earlier blog post on Shrinking the Transaction Log files in SQL Server, I have already outlined the usage of the Transaction Log File(s) during normal operation of the database. Again, when a database starts up, it has to go through the recovery process. The recovery process consists of 3 distinct phases – analysis, redo and undo.
During the analysis phase, a background thread reads the Transaction Log File(s) of the database, from the last marked CheckPoint till the end of the file, and analyzes the records in the Transaction Log File(s). If this phase completes successfully, the recovery process moves to the next phase called the redo phase, and later into the undo phase.
A failure in any of these phases can cause the recovery process to fail; there by causing the database to be marked suspect (a database is marked suspect when the database cannot be recovered). More often than not, in case of Log File corruption, the recovery process fails during the analysis phase. Any failure during recovery is recorded in the SQL Server ErrorLog file, and the information in this file can give deep in site into the problem on hand.
Note: Log File corruption is just one of the reasons why recovery might fail for a database. Recovery might also fail when the mdf or the ndf files are corrupt. In these cases, recovery mostly fails during the redo/undo phases.
Next steps when a Log File is found to be corrupt:: Step 1
The first step that you should consider when you find the Log File to be corrupt, is to analyze the Windows Application and System Event Logs for any hardware issues. If you can spot any hardware issue, including any disk issue, please engage your hardware vendor immediately. If a database has become corrupt once due to a hardware issue, it will mostly likely become corrupt again if the hardware issue is not resolved.
Next steps when a Log File is found to be corrupt:: Step 2
The obvious next step is to restore the database from the last good known backup. Restore the last Full Backup, the last Differential Backup taken after the applied Full Backup, and all Transaction log Backups taken after the applied Differential Backup, in order. This will ensure that the transactional consistency of the database is maintained and the database is recovered to a known point in time.
However, if restoring the database from the last good known backup is not an option, then the only other option is to try and rebuild the Log File(s). Since there is no way in which we can repair a corrupt Transaction Log File, our best shot is to rebuild the Log File(s).
Before you rebuild the Transaction Log File, please go through the article When should you rebuild the transaction log? by the SQL Server Storage Engine Team.
To rebuild the log files, follow the following steps:
-
Detach the database using the sp_detach_db command. You might need to alter the database and change it to emergency mode before you can detach the database. For details on how to change the database state to Emergency, please see the following MSDN article: http://msdn.microsoft.com/en-us/library/ms174269(SQL.90).aspx
Syntax:
ALTER DATABASE database_name
{
<set_database_options>
}
[;]
<set_database_options>::=
SET
{
{ <optionspec> [ ,...n ] [ WITH <termination> ] }
}
<optionspec>::=
{
<db_state_option>
}
<db_state_option> ::=
{ ONLINE | OFFLINE | EMERGENCY }
-
Now, delete or rename the corrupt Transaction Log File(s). You can now, use the Create Database with the FOR ATTACH_REBUILD_LOG option to attach the database while rebuilding the Log File(s):
Syntax:
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH_REBUILD_LOG }
[;]
According to the Books OnLine (http://msdn.microsoft.com/en-us/library/ms176061(SQL.90).aspx):
FOR ATTACH_REBUILD_LOG
Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log file is rebuilt. There must be a <filespec> entry specifying the primary file.
Note:
If the log files are available, the Database Engine will use those files instead of rebuilding the log files.
FOR ATTACH_REBUILD_LOG requires the following:
=> A clean shutdown of the database.
=> All data files (MDF and NDF) must be available.
Important:
This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed.
Next steps when a Log File is found to be corrupt:: Step 3
If Step 2 fails, there is another way to rebuild the Transaction Log File. However, before I explain this step, I would request all my readers to go through the disclaimer below:
THE COMMAND IS AN UNDOCUMENTED AND UNSUPPORTED COMMAND THAT CAN BE USED IN EMERGENCY SITUATIONS WHERE THE DATABASE IS OFFLINE BECAUSE OF SOME CATASTROPHIC PROBLEM WITH THE TRANSACTION LOG. IT IS DESIGNED TO DESTROY THE ENTIRE CONTENTS OF THE CURRENT TRANSACTION LOG, REBUILD A NEW ONE, AND ALLOW THE USER TO BRING THE DATABASE BACK ONLINE. HOWEVER, THIS MAY RESULT IN MORE DATA LOST THAN IF THE LAST KNOWN, GOOD BACKUP WAS RESTORED. AS SUCH, IF YOU CHOOSE TO USE THE COMMAND WITH OR WITHOUT THE ASSISTANCE OF MICROSOFT PSS, MICROSOFT STRONGLY RECOMMENDS YOU BACKUP ALL OF THE DATABASE AND TRANSACTION LOG FILES ASSOCIATED WITH THE DATABASE FOR THE COMMAND AS A METHOD TO RECOVER FROM PROBLEMS WHERE THE COMMAND IS BEING CONSIDERED.
IF THIS COMMAND IS SUCCESSFUL AND THE DATABASE IS BROUGHT BACK ONLINE, THE DATABASE MAY BE IN A PHYSICALLY AND LOGICALLY INCONSISTENT STATE. THE ACID PROPERTIES OF ATOMICITY AND CONSISTENCY ARE NO LONGER GUARANTEED. THE DBCC CHECKDB COMMAND CAN BE USED TO DETERMINE WHAT PHYSICAL PROBLEMS MAY EXIST. HOWEVER, THERE IS NO METHOD TO DETERMINE WHAT LOGICAL INCONSISTENCIES EXIST WITHIN THE DATABASE ASIDE FROM THE USER’S OWN MANUAL INSPECTION OF THE DATABASE TABLES. THIS MEANS THAT THERE IS NO METHOD TO DETERMINE IF THE DATABASE PAGES THAT EXIST WITHIN THE DATABASE REFLECT THE CORRECT COMMITTED STATE OF THE DATABASE WHEN IT WAS LAST TAKEN OFFLINE.
-
ALTER DATABASE database_name
{
<set_database_options>
}
[;]
<set_database_options>::=
SET
{
{ <optionspec> [ ,...n ] [ WITH <termination> ] }
}
<optionspec>::=
{
<db_state_option>
}
<db_state_option> ::=
{ ONLINE | OFFLINE | EMERGENCY }
-
Now, execute the command:
DBCC REBUILD_LOG ('<database_name>', '<log_file_name>')
where <database_name> is the name of the database and <log_file_name> is the physical path of the new log file, not a logical file name. If you do not specify the full path, the new log is created in the same directory as the primary database file (.mdf). Therefore, if the log file already exists in that path, use a different name for the new log.
The log should now be rebuilt and you can now run the DBCC DBRECOVER statement to bring the database back online.
Syntax:
DBCC DBRECOVER ('<database_name>')
Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.
