ERROR : 9004 An error occurred while processing the log for database.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.
If you receive above error it means you are in great trouble. This error occurs when database is attempted to attach and it does not get attached. I have solved this error using following methods. Hope this will help anybody who is facing the same error.
Microsoft suggest there are two solution to this problem.
1) Restore from a backup.
- Create Empty Database with same name and physical files (.ldf and .mdf).
- Shut down SQL Server.
- Replace the files which you want to attach with this new empty database files.
- Start SQL Server.
- Database will be in suspect mode which means so far everything going as it should be.
- Next is to put database in emergency mode. ALTER DATABASE <DatabaseName> SET EMERGENCY will change the database status to emergency.
- A database which is in emergency mode can be repaired with allowing some data loss. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS will do the task.
- In this process some of the data may be lost but database will in working condition with most of the data retrieved from log.
2) Rebuild the log.
- Create database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.
- This may fix the problem right away if database which is attempted to be attach is shut down properly. If database is corrupt it will be not restored correctly.
- Next DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be ran to bring back the database operational. This may loose some data but it may bring back database in working condition.
- In this option as log is rebuilt SQL Server does not use log file to retrieve any data. In this process none of the log operation like transaction rollback will work.
I prefer to use the first method if it works. First method has worked sevral time without any issue. Syntax of the CREATE database can be found on Book Online for further guidance.
Reference : Pinal Dave (https://blog.sqlauthority.com)