SQL SERVER – FIX – 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.

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 (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – FIX – 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.

  1. 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 .

    Like

  2. 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.

    Like

  3. 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’.

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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 :)

    Like

  7. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s