Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6

    Unanswered: Errors attaching a database after tlog filled.

    I have a database, "heat", that crashed after the transaction log filled the HDD.
    Since then I have been unsuccessful in bring the database back up.

    I'm not concerned with restoring what's in the transaction log, only the data file.

    The current state is as follows:

    The database is not attached.

    When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."

    I noticed in the forum the same error, so I tried the fix provided which was to attach only the data file. I renamed the log file, and tried to attach the data file but received the following error:

    Error 1813: Could not open new database "heat". CREATE DATABASE is aborted.
    Device activation error. The physical file name "D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF may be incorrect.


    Any help would br greatly appreciated.
    -Andy

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What commands are you using to attatch the database ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6
    I'm using the Attach database command in enterprise manager.

    Ok, I restored a backup of the database, then pulled a switcharoo (I have copies of all of the files, so I'm not overwriting anything).
    The database is now suspect. Any ideas?
    Last edited by monza700; 11-19-03 at 14:09.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    try using sp_attach_single_file_db 'dbname','Physical path of the MDF file'

    dont know about EM though , never use it at all
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Errors attaching a database after tlog filled.

    Originally posted by monza700
    When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."
    Just a tad confusing...you don't attach using the tranny...

    you don't mean that, right?

    How did you clean up the hard drive?

    What % is left

    You need to set up alerts to tell you you're at a threshhold...

    How big is the db btw, and the hard drive?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6
    I tried running the sp_attach_single_file_db command and received the following:

    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'heat'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF' may be incorrect.

    Thanks for any/all help.

  7. #7
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6

    Re: Errors attaching a database after tlog filled.

    In response:

    Q: you don't mean that, right?
    A: No, sorry, I'm trying to attach the mdf file.
    Q: How did you clean up the hard drive?
    A: deleted unneeded backups that were on the same drive.
    Q: What % is left
    A: 6GB
    Q: You need to set up alerts to tell you you're at a threshhold...
    A: I will do that once I get the database fixed (hindsight)
    Q: How big is the db btw, and the hard drive? [/SIZE][/QUOTE]
    A: about 1GB.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, you restore the mdf

    Second, I think (only on rare occasions that is), to attach, you first need to have dettached...

    Why not just do a RESTORE?

    Like:

    Code:
    ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE TaxReconDB_Prod
       FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
       WITH   MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF' 
            , MOVE 'TaxReconDB_Log'  TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF'
    	, REPLACE
    
    
    ALTER DATABASE TaxReconDB_Prod SET READ_WRITE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you notice, the restore takes tyhe mdf and the ldf from the last time the database was dumped....

    the ldf will contain all uncommitted transactions..

    If you did tranny dumps after that, and want/need to recover them, they'll have to be applied as well, in order...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6
    No dump files (dmp) are available.
    Unfortunately I came in sort of after the fact. And some people thought they would try deleting the transaction log.
    I also found out that before any of this happened the database was set to FULL recovery mode. Would that be causing any issues?

    Also, the database has already be detached.

    (I'm feeling a Microsoft Tech Support call coming up).

    Thanks again,
    Andy

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There's nothing in

    D:\Microsoft SQL Server\MSSQL\data\

    with a .mdf extension?

    (or ,bkp perhaps?)

    Can you look in Enterprise manager to see if there are any maint. plans set up., or jobs running maint. stored procedures?

    That's very odd...

    Tecjh support can't help you with what you don't have....

    And Database recovery FULL is no problem....just have to make sure to dump the tranny logs with a scheduled job...

    If it's no big deal you could use simple...

    Then just dump the database...

    What did this poor database do, to deserve this?

    And what did use to contain, just out curiosity?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2003
    Location
    Orlando, FL
    Posts
    6

    Found a solution...

    Ok, the solution went something like this:
    Restored an old backup of the database.

    Stopped the SQL service.

    Replaced the new datafile with the old data file.

    Started the SQL service.

    Ran the following code on it (placed the database into a readonly, single user, dbo state a.k.a. Emergency state):
    Code:
    sp_configure 'allow updates',1
    go
    reconfigure with override
    go
    update sysdatabases
    set status=-32768
    where name = 'heat'
    go
    sp_configure 'allow updates',0
    go
    reconfigure with override
    go
    Copied all of the objects and data from the bad database into a new one.

    Deleted the bad database.

    Copied all of the objects and data from the new database into another database with the name of the original.

    Everything is A-O.K.

    Thanks again for all of your help.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •