November 16, 2007 at 12:44 pm
Today I noticed that my server had only 10MB free space; the problem was that the database logs for a very small database (less than 10 tables, and probably less than 1000 records in total) were taking up 230GB. The .mdf file for that database was less than 4MB, but the .ldf was 230GB.
I tried to detach the database but I was unsuccessful, Management Studio crashed. I had to free up space fast because there were many other more important things running on that box so I decided to drop the database. That didn't work either. I had to stop the SQL Server service and delete the .mdf and .ldf by hand. When I restarted the server I was able to get rid of the database.
Now I'm looking at a 2.5MB backup file that is half a day old that I would like to restore and I can't. If try to restore it MS tells me that it needs 230GB of space. If I redirect the restore to a drive that has that much space it goes to 90% and then it hangs -- I've waited for 15 minutes and the progress didn't move from 90%.
Any ideas how I could restore the backup?!
Thanks!
November 16, 2007 at 1:55 pm
Well, I would wait longer than 15 minutes and see what happens. Don't trust the progress messages, just wait a bit and see what happens. It will take Windows a fair amount of time to create the 230 GB log file. You could go out to Windows Explorer and watch as SQL Server creates the file. This may give you a better gauge as to how close you are to being done.
November 16, 2007 at 2:05 pm
OK, I'll be more patient 🙂
November 16, 2007 at 4:45 pm
OK, after about two hours it finished and it told me the restore was successful, but now when I try to access the database MS tells me the database is "Restoring..." and if I try to do anything it tells me that the "Database cannot be opened. It is in the middle of a restore"
Any ideas?
November 16, 2007 at 4:56 pm
Take a look in Activity Monitor (Mngm Studio, under Management node) to see if your db is still restoring; if not then run
RESTORE your_db WITH RECOVERY
November 16, 2007 at 4:59 pm
As a matter of fact, just change your db recovery model to simple if you do not need log backups; this will keep an appropriate size for your ldf file;
good luck
November 16, 2007 at 5:01 pm
There's nothing accessing the database -- I even restarted the service. It took me close to two and half hours to restore it the first time -- can't remember if I told it to restore with recovery or not. I guess if there's no other way I will have to drop the database and try again... :ermm:
November 16, 2007 at 5:03 pm
I will have to do that in the future; but I sure would like to know how come it grew from a few megs to 230GB in one day... (the log file ie)
November 16, 2007 at 5:03 pm
don't drop it yet; just run the WITH RECOVERY query from a new query window
November 16, 2007 at 5:06 pm
http://support.microsoft.com/kb/317375
this will help
November 16, 2007 at 5:06 pm
I tried that "RESTORE MyDB WITH RECOVERY" and I get this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
November 16, 2007 at 5:14 pm
Thanks, I will use that when I'll look over the application that uses that database to figure out the problem.
November 16, 2007 at 5:34 pm
michaela (11/16/2007)
don't drop it yet; just run the WITH RECOVERY query from a new query window
I tried "RESTORE DATABASE MyDB WITH RECOVERY" and it worked!! :w00t: (I didn't put "database" the first time, that's why it was giving me the syntax error). Thank you, thank you!! After I was able to access the database I ran a shrink on the log file and it went from 230GB to 1MB.
Thank you all for you help!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply