As we all know .ldf file are essential components to maintain the consistency of the transaction log file. But what happen when it is found that there is corruption in your transaction log file ? I found many queries like this, look at the following query:
“My system suddenly got shutdown, I am experiencing some hardware issue and a faulty memory which lead to this unclean shutdown. When the system came online, the database becomes inaccessible. Trying to bring database online and seeing the error messages, we got to know that our transaction logs are corrupted.What to do ? How to repair log file corruption? Please help me out from this situation.”
Maybe you are also dealing with same type of situation. I will help you out, from this situation. When database starts up it has to go through three phases- Analysis, Redo and Undo. Failure in any of the phases causes failed recovery process causing database in Suspect mode. In case of Log file corruption, recovery process fails during the analysis phase.
But first let us discuss, what are the possible reasons of getting log file corrupted.
Possible Causes Of SQL Transaction Log File Corruption
- Malicious Software
- System Terminating Abnormally / Unclean Shutdown of SQL Server
- If the data exceeds the limit of log file
These could be the possible reasons behind .ldf file getting corrupted.
How to Recover Transaction log file ??
Due to some known or unknown possible reason of transaction log file corruption, .mdf file is not accessible to the SQL Server.
First of all, we need to find whether there is backup of log file or not , as we can’t afford to loose data at any cost.
Found Log File Corrupted ?
First of all you need to find out the reason behind the log file corruption, whether the corruption is due to hardware failure or malware virus or any possible causes.
When you found out the log file is corrupted, Check Windows application and System event logs for hardware issue. Contact the hardware vendor as there is possibility of corruption getting back again.
In this case you can restore your database from good known backup. Restore last full backup & differential backup taken after full backup, and all transaction backup taken after differential backup. This will maintain the transactional consistency of the database and it can be recovered to point in time.
What if there is no backup ?
The only option you have is to rebuild the log Files. For rebuilding log file, perform following steps:
- Take backup of your MDF File
- First detach the database by using sp_detach_db command. You might need to alter the database and change it in emergency mode before detaching the database.
- Delete the corrupt transaction log file.
- Now run below command to attach the database for rebuilding log file.
CREATE DATABASE database_name
ON
FOR { ATTACH_REBUILD_LOG } ;
This will rename the corrupted file as newly created file.
Through these steps, you will be able to repair your MDF file. However, this might result in data loss. For Quick recovery of MDF file, you can try MDF Recovery Tool to repair SQL database objects like tables, functions, triggers etc.
Conclusion
There are several known and unknown reason behind corruption in log file. Some of them are listed above. The blog discusses the solution to the DBAs who find it difficult to repair SQL transaction log file. It also covers the quick solution to recover Log file corruption.
EmoticonEmoticon