Post reply

Transaction log too large to Restore

  • March 19, 2007 at 10:31 am

    #119194

    Had a db that got a 150GB log file and was bringing down databases. The db was in a state that did not allow me to do anything with it. I had to detach, but it never did detach smoothly. It eventually disappeared from the list of databases so I could delete the log file.

    I thought I could just reattach the database, without a log file and it should come back up. At least that was the case with sql 2000. However, I'm getting the following error in doing this:

    Unable to open the physical file "e:\filename_log.ldf". Operating system erorr 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

    Now, I know the file doesn't exist, but I was certain that in 2000, it would simply create it for you anyway even if it didn't exist.

    I also have a SQL backup of the db, but the problem is that I don't have enough storage space to restore it with the transaction log. So this option is bad as well if I can't restore a database without separate from the transaction log.

  • March 19, 2007 at 11:16 am

    #695775

    Can you restore the backup to another database server with enough space, shrink down the log file, take a backup and then restore it?

     

    K. Brian Kelley
    @kbriankelley

  • March 19, 2007 at 2:01 pm

    #695800

    Try

    CREATE DATABASE FOR ATTACH_REBUILD_LOG option...

    See BOL for details...

    http://msdn2.microsoft.com/en-us/library/ms176061.aspx

     

    MohammedU
    Microsoft SQL Server MVP

  • March 19, 2007 at 10:44 pm

    #695843

    Peter.

    In SQL 2005 you can reattach without the log file.  After you select the file to reattach, in the lower part of the screen in the database details section, it lists the two (or more) files.  You need to select the LOG file entry and then click the Remove button below this listing.  This will remove the log file(s) and when you OK button to attach the data, it should create the log file anew for you. 

    SJ

    Accepting all invites

  • March 20, 2007 at 8:12 am

    #695939

    Thanks everybody for all the help. I tried the attach as described in your instructions, but I found out that the db did not come down cleanly, so it wouldn't allow it. So, I ended up having to restore the backup file on a box with enough storage to handle the size, and then shrink it down and then restore it back to the original location. But, I love those ideas on the attach -- it'll have those in my back pocket for any future issues. It knew it had to be possible with 2005, I just couldn't figure it out.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply