Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't restore database / Log issues?!


Can't restore database / Log issues?!

Author
Message
Peter Zolja
Peter Zolja
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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!
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4394 Visits: 4529
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
Peter Zolja
Peter Zolja
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
OK, I'll be more patient Smile
Peter Zolja
Peter Zolja
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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?
michaela
michaela
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 440
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



michaela
michaela
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 440
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



Peter Zolja
Peter Zolja
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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... Ermm
Peter Zolja
Peter Zolja
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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)
michaela
michaela
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 440
don't drop it yet; just run the WITH RECOVERY query from a new query window



michaela
michaela
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 440
http://support.microsoft.com/kb/317375
this will help



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search