|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10,
Visits: 18
|
|
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!
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 3,392,
Visits: 2,719
|
|
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.
John Rowan
======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10,
Visits: 18
|
|
| OK, I'll be more patient :)
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10,
Visits: 18
|
|
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?
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 10:05 PM
Points: 1,301,
Visits: 297
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 10:05 PM
Points: 1,301,
Visits: 297
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10,
Visits: 18
|
|
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... 
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10,
Visits: 18
|
|
| 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)
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 10:05 PM
Points: 1,301,
Visits: 297
|
|
don't drop it yet; just run the WITH RECOVERY query from a new query window
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 10:05 PM
Points: 1,301,
Visits: 297
|
|
http://support.microsoft.com/kb/317375
this will help
|
|
|
|
