SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HUGE log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  09:28:20  Show Profile  Reply with Quote
I came in to work today and one of my databases now has an LDF log file that is 200 gigs. The database is extremely small. It has a dozen or so functions, no views, and about 15 small tables. How did it get so big? Also, how do I resolve this situation? I tried backing it up, deleting it, and then restoring, but it restores the log file again to an even larger size.

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/02/2009 :  09:35:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
After a backup, change recovery mode to simple and then shrink the log file.
Change back to whatever mode you are using today.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  09:43:46  Show Profile  Reply with Quote
Thanks, Peso. Where/how do I change the recovery mode?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/02/2009 :  09:45:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.google.co.uk/search?hl=en&safe=off&q=sql+server+change+recovery+mode+site%3Amicrosoft.com&meta=


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  09:48:34  Show Profile  Reply with Quote
Well... one of the problems is now I don't have the database restored and can't seem to restore it because the LDF file it creates is bigger than it was when I deleted it. It gets to 90% in the restore and hangs with the LDF file being 198 gigs. So, unless I start deleting other files on my hard drive to make more room for the restore, I can't restore it.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/02/2009 :  09:52:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Restore?
Do a FULL backup first.
Then change recovery model.
Then shrink log file.
Then change recovery model to the model you had when you started.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  09:57:49  Show Profile  Reply with Quote
I think there is some confusion. I made a full backup first. Then I deleted the database on my server because there wasn't enough room on my hard drive to restore the database. If I understand correctly, to change the recovery model, the database must exist on your server. That isn't so in my case.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/02/2009 :  10:06:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
When you restore, the files are EXACTLY as they were during backup...
You can however, if both files still are on disk, attach only the data file and have sql server create a new log file for you...



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  10:08:27  Show Profile  Reply with Quote
That sounds like what I'll need to do. How do I do that?
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  10:17:22  Show Profile  Reply with Quote
Also, the restore is apparently not the same. When I deleted the database before, the LDF file was 196 gigs and when I try to restore it now from the backup, the file gets to 198 gigs and the backup can't even complete because my computer runs out of disk space.
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  11:04:09  Show Profile  Reply with Quote
I guess I'll figure out a way to get more HD space. I tried the recovery mode switch on another database and it allowed me to substantially shrink the log file. Thanks for that.

How does this happen, though (the log file just getting ridiculously big)?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/02/2009 :  11:48:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe a massive number of updates, inserts or deletes?
Creating an index? Dropping an index?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

im1dermike
Posting Yak Master

222 Posts

Posted - 02/02/2009 :  11:56:51  Show Profile  Reply with Quote
There aren't that many inserts done. Less than a thousand records.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/02/2009 :  13:23:08  Show Profile  Reply with Quote
quote:
Originally posted by im1dermike

I guess I'll figure out a way to get more HD space. I tried the recovery mode switch on another database and it allowed me to substantially shrink the log file. Thanks for that.

How does this happen, though (the log file just getting ridiculously big)?



You should be doing regular transaction log backup like every 30 mins to avoid that in Full Recovery model.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/03/2009 :  00:30:47  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
What recovery model and are you doing transaction log backups?

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000