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)
19 Comments. Leave new
Sir
I am working in Microsoft Technology past 4 year now i am need to learn ssis and ssas regarding this i need some materials u can guide and send some tips to learn it shortly. Regarding this mial me .
Thank you Pinal, this solution solved my problem :)
Hello,
This error can result from a synchronization problem between replication agents.
to repare, stop replication for the database mentionned in the error and launch sp_replrestart.
Your solution 1 works for me. Thanks a lot!
Hi ,
I tried all the options mentioned above and still I am getting the same error
ALTER DATABASE a SET EMERGENCY
update sysdatabases set status = 32768 where name = ‘a’
go
use a
go
DBCC checkdb (‘a’, repair_allow_data_loss)
— Error Server: Msg 5180, Level 22, State 1, Line 1
— Could not open FCB for invalid file ID 0 in database ‘a’.
DBCC DROPCLEANBUFFERS
— Error Server: Msg 5180, Level 22, State 1, Line 1
— Could not open FCB for invalid file ID 0 in database ‘a’.
DBCC FREEPROCCACHE
— Error Server: Msg 5180, Level 22, State 1, Line 1
— Could not open FCB for invalid file ID 0 in database ‘a’.
Dbcc REBUILD_LOG (‘a’,’D:\Program Files\MSSQL\Data\a_log.ldf’)
— Error Server: Msg 5180, Level 22, State 1, Line 1
— Could not open FCB for invalid file ID 0 in database ‘a’.
Thanks for the help!
Solution 1 it worked fine for me, just is needed add to put the database in single mode before run DBCC.
Regards from Argentina.
Jose
I do as it says
ALTER DATABASE a SET EMERGENCY
update sysdatabases set status = 32768 where name = ‘a’
go
use a
go
DBCC checkdb (‘a’, repair_allow_data_loss)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Dbcc REBUILD_LOG (‘a’,’S:\{my path}\MSSQL\Data\a_log.ldf’)
BUT
it gave me this error
On
update sysdatabases set status = 32768 where name = ‘a’
IT GAVE ME THE FOLLOWING ERROR
Ad hoc updates to system catalogs are not allowed
On
use a
AND
DBCC checkdb (‘a’, repair_allow_data_loss)
IT GAVE ME THE FOLLOWING ERROR
Cannot open database ‘a’ version 539. Upgrade the database to the latest version
What should i do ?
thanks
SQL DATA RECOVERY SOFTWARE
Replace is not allowed In sqlserver 2005 when I created the Datafiles with same name as of MDF and LDF…
Thanks for this. Option 1 was very helpful
Thanks for this – option 1 guided me in the right direction.
DBCC CheckDB (dbname, REPAIR_ALLOW_DATA_LOSS) this command is taking long time and even when this is executing no messsages are diaplaying.
we just want make sure its executing properly or not. How to check this?
Please help :)
Hi sir
I am using SQL server 2012 and the same is taking almost 90% physical memory , kindly helpus
I was getting the same error
Error: 9004, Severity: 23, State: 6.
An error occurred while processing the log for database ‘model’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Initially I was really worried that I might need to recreate the DBs.
But then on one colleague’s suggestion I just replaced the model.mdf and modellog.ldf from a working SQL server into my own and it started working without any issues. I understand that it is not a fullproof solution as might loose some data but still it is the fastest solution.
Thank you VERY much.
This solution of yours was very easy and worked.
I had relocated the system databases and accidentally deleted the model db from the wrong directory, I restored it from the trash bin, but now I am receiving this error and immediately after wards it does this: SQL Server shutdown has been initiated
I have no way of opening the instance.
I have no log file, only MDF file is there and it is having open transactions/users problem, no checkpoint problem, i have applied following two method, but it did not work
— Method 1:
EXEC sp_attach_single_file_db @dbname=’Nature001′,
@physname=N’D:\TFATDATA\Nature001.mdf’
GO
— Method 2:
CREATE DATABASE Nature001 ON
(FILENAME = N’D:\TFATDATA\Nature001.mdf’)
FOR ATTACH_REBUILD_LOG
GO
please help
Awesome Article, worked for me even when the database was upgraded. Successfully able to attach database with Option 1.
Only thing to add is I had to take it to Single user mode and then run the repair command.
After SET_EMERGENCY ->
ALTER DATABASE xyz SET single_user with rollback immediate
dbcc checkdb(‘xyz’,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE xyz SET MULTI_USER.
Thanks for your help Pinal!!
great guide, thank you for sharing