Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can't restore database / Log issues?! Expand / Collapse
Author
Message
Posted Friday, November 16, 2007 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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!
Post #423199
Posted Friday, November 16, 2007 1:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 3,840, Visits: 3,853
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
Post #423221
Posted Friday, November 16, 2007 2:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 10, 2009 4:12 AM
Points: 10, Visits: 18
OK, I'll be more patient :)
Post #423228
Posted Friday, November 16, 2007 4:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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?
Post #423259
Posted Friday, November 16, 2007 4:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
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



Post #423261
Posted Friday, November 16, 2007 4:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
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



Post #423262
Posted Friday, November 16, 2007 5:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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...
Post #423263
Posted Friday, November 16, 2007 5:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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)
Post #423264
Posted Friday, November 16, 2007 5:03 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
don't drop it yet; just run the WITH RECOVERY query from a new query window


Post #423266
Posted Friday, November 16, 2007 5:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
http://support.microsoft.com/kb/317375
this will help



Post #423267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse