Transaction log too large to Restore

Question

Transaction log too large to Restore

Perry Citrowske

Ten Centuries

Points: 1243
More actions
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.

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

MohammedU SSCoach Points: 19644 More actions · Answer 2

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

swjohnson Hall of Fame Points: 3254 More actions · Answer 3

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

Perry Citrowske Ten Centuries Points: 1243 More actions · Answer 4

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.