How to rebuild corrupt transaction log?

In the previous article we have discussed about “how to restore MS SQL database without transaction log file”. In this article we have discussed about how to deal with error 9004 in Microsoft SQL server database. Error 9004 occurs when you try to attach SQL server database and the an error message appears look like

“Error: 9004, Severity: 21, State: 1.
An error occurred while processing the log for database 'employee'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”

Cause for Error 9004: This error message is related to the corrupt transaction log. It is a general error message that indicates there is some corruption in the transaction log. There can be several possible cause for above error like hardware error, software malfunction, corrupt drivers, SQL server engine problem etc.

Possible Solution: when you got error 9004, you have three options to resolve this error message.

First solution: you can restore from the clean backup to recover SQL server from above problem.

Second solution: if you can not recover from the backup then you can rebuild the transaction log. For creating transaction log, you should follow the given below steps:

• Create a new empty database with same name and physical file layout.
• Shutdown the SQL server database.
• Swap the files in empty database which you want to attach.
• If database goes in suspect mode then set database in emergency mode.
• Run run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.
• Fix the issues.

Third solution: You can also try “CREATE DATABASE FOR ATTACH_REBUILD_LOG”. This command will run successfully when you have shutdown SQL server properly.

Note: second solution will pull data as much as possible from the corrupt transaction log but be sure there may be some data loss.

Fourth solution: Use a powerful SQL Database Recovery application.