| Author |
Topic |
|
im1dermike
Posting Yak Master
222 Posts |
 Posted - 2009-02-02 : 09:28:20
|
| 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
30421 Posts |
Posted - 2009-02-02 : 09:35:49
|
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" |
 |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 09:43:46
|
| Thanks, Peso. Where/how do I change the recovery mode? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 09:45:43
|
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" |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 09:48:34
|
| 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 09:52:20
|
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" |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 09:57:49
|
| 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 10:06:05
|
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" |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 10:08:27
|
| That sounds like what I'll need to do. How do I do that? |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 10:17:22
|
| 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. |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 11:04:09
|
| 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)? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 11:48:24
|
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" |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-02-02 : 11:56:51
|
| There aren't that many inserts done. Less than a thousand records. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 13:23:08
|
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. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-03 : 00:30:47
|
| What recovery model and are you doing transaction log backups?--Gail ShawSQL Server MVP |
|
|
|
|
HUGE log file