none
Attach database failed error 9004

    Question

  • Hi,

    i need to attach my db, but i receive this error:

    an error occurred while processing the log for database 'xyj'.

    Could not open database 'xyj' . Create database is aborted. (Microsoft Sql Server,

    error 9004).

    Can you help me?

    Tuesday, August 01, 2006 8:11 AM

Answers

  • You have two options:

    1. You can create an empty database with the same name and physical file layout, shut down the server, swap in the files you want to attach in place of the empty DB files, and start the server.  The database should come up in suspect mode.  You can then ALTER DATABASE <foo> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.  This will pull as much data as possible out of the log to make the database consistent, but may have to delete some data in order to make the database consistent.  This is the option which is most likely to get the maximum data back.
    2. You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back.  If the database was cleanly shut down, you MIGHT be able to succeed.  There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back.  You should in any event run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent.  In this event, SQL Server will make no attempt to mine information from the log.  It will ignore the contents of the log.  If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required.

     

    Wednesday, August 02, 2006 9:08 PM
    Moderator

All replies

  • You have two options:

    1. You can create an empty database with the same name and physical file layout, shut down the server, swap in the files you want to attach in place of the empty DB files, and start the server.  The database should come up in suspect mode.  You can then ALTER DATABASE <foo> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.  This will pull as much data as possible out of the log to make the database consistent, but may have to delete some data in order to make the database consistent.  This is the option which is most likely to get the maximum data back.
    2. You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back.  If the database was cleanly shut down, you MIGHT be able to succeed.  There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back.  You should in any event run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent.  In this event, SQL Server will make no attempt to mine information from the log.  It will ignore the contents of the log.  If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required.

     

    Wednesday, August 02, 2006 9:08 PM
    Moderator
  • hi gigi,

    could it be that

    sql server wasn't able to find

    the log in the same directory as the db file.

    you can use enterprise manager to point to the log files

    or specify the location of the log files in the sp attach db command

     

    EXEC sp_attach_db @dbname = N'pubs', 
       @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
       @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
    
    regards
    Friday, August 04, 2006 6:22 PM
  • Dear Kevin,

    i want to thank you very much for your help... the db is ok now...

    Best regards

    Gigi

    Monday, August 07, 2006 10:49 PM
  •  

    Dear joeydj,

    i want to thank you very much for your help... the db is ok now...

    Best regards

    Gigi

    Monday, August 07, 2006 10:50 PM
  • I have these exact problem but on sql server 2000.

    client was not backing up databases and after a power glitch, there is disk corruption - all the files are on the file system but I cannot start the instance

    i tried to attach any of the user database files to another instance on another box and get the same error

    Error: 9004 An error occurred while processing the log for database

    Now, databases were in full recovery mode, no backups ... please advice

    sql server 2000 sp4

    Wednesday, September 27, 2006 8:18 PM
  • have these exact problem but on sql server 2000.

    client was not backing up databases and after a power glitch, there is disk corruption - all the files are on the file system but I cannot start the instance

    i tried to attach any of the user database files to another instance on another box and get the same error

    Error: 9004 An error occurred while processing the log for database

    Now, databases were in full recovery mode, no backups ... please advice

    sql server 2000 sp4

    Wednesday, September 27, 2006 9:31 PM
  • hi maiko,

    the solution that i used to solve my proble was:

     - create a new db with the same name of the lost db;

     - put the db in emergency mode;

     - stop sql service and replace the mdf file;

     - start sql service;

     - the db is probably corrupted for the disk crash...and you have to reindex all table.

     

     bye

     Gigi

     

    Sunday, October 01, 2006 3:33 PM
  • Thanks Kevin.
    Your suggestion saved me.
    I did not follow it completely but it point me the right direction.

    I had an other machine that crashed, I had the mdf and log file, but the new system did not want to import it.
    Also it did refuse to restore the backup because it was a different database (through it had the smae data structure).
    After swapping the files, I was able to restore from the backup
    Thanks a lot        
    RNBY  
    Thursday, July 12, 2007 8:30 AM
  • Hi Kevin,

     

    I tried your solution 1 above. My situation was a database for some reason became detached and the LDF was missing. I could not attached the database back because it is looking for the LDF file. I tried your suggestion # 1 above but did not work. Do you have any other suggestion?

     

    WakinLee

     

     

    Monday, February 25, 2008 9:01 PM
  •  

    Hi ,

     

    I also faced the same problem yesterday, but I tried a new solution and it worked. Well I restore the database from the last backup and then this time itself give the new locations of the datafile or logfile and it worked. The database is up and running fine and my problem also solved i.e. changing the location of the logfile, datafile, which i was unable to do with attach and dettach.

     

    Cheers,

    Najeeb

    Sunday, June 29, 2008 6:01 AM
  •  

    Tuesday, July 01, 2008 9:49 AM
  • hi Kevin,
               i am cmng accros same problem..but i an\m not able to attach the database.

    i am not able to understand "You can then ALTER DATABASE <foo> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS."
    how to set emergency.from i have stop services for server first..is that right..

    please help me its urgent.please mail the solution .please.and my datbase is of 1GB in size
    email :- swapnil.r.cool@gmail.com
    Sunday, July 05, 2009 2:01 PM
    1. create a new database with the same name as your problem Database
    2. stop the SQLServer Service
    3. replace the .mdf file of new database with the .mdf file of your problem database
    4. starts the SQLServer Service
    5. execute this query on master Database:

    ALTER DATABASE <dbName> SET emergency
    GO
    ALTER DATABASE <dbName> SET single_user
    GO
    DBCC checkdb (<dbname>, repair_allow_data_loss)
    ALTER DATABASE <dbName> SET multi_user
    GO

     

    Friday, October 28, 2011 10:34 PM
    1. create a new database with the same name as your problem Database
    2. stop the SQLServer Service
    3. replace the .mdf file of new database with the .mdf file of your problem database
    4. starts the SQLServer Service
    5. execute this query on master Database:

    ALTER DATABASE <dbName> SET emergency
    GO
    ALTER DATABASE <dbName> SET single_user
    GO
    DBCC checkdb (<dbname>, repair_allow_data_loss)
    ALTER DATABASE <dbName> SET multi_user
    GO

    Friday, October 28, 2011 10:36 PM
  • Read related article here: How to Resolve MS SQL server error 9004?

    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, March 07, 2012 5:25 AM