March 13, 2007 at 7:36 am
One of my IT people is having an issue with log files not shrinking when running a Maint plan. Below is what he wrote to me.
SQL Server 2000
I run the Optimization Job right now by itself, the log file will grow from 1mb to 13gigs. The job will finish successfully but the file will never shrink.
What I do to shrink it down is run a Query -- BACKUP LOG DFS with TRUNCATE_ONLY -- then I run -- DBCC SHRINKFILE(DFSfixed_log, 1) --. That takes it right down to a few kb. I also run -- DBCC SHRINKDATABASE (DFS, 10) -- manually to knock down the DFS database. I know this is part of the Integrity Job but even that doesn't seem to do much through the Maintenance Plan. What it comes down to is, if I let the Maintenance Plan run on it's own, the backup jobs run fine. The Integrity and Optimization Jobs do not.
Thanks in advance if any one has any ideas,
John Robertson
March 13, 2007 at 8:18 am
Do you have the option to Shrink the file checked? What are the values for "Shrink database when it grows beyond" and "Amount of free space to keep after shrink"? Is your database set for Full or Simple recovery mode? If Full, are you backing up the Transaction Log? When are you doing the shrink? Before or after the backups?
-SQLBill
March 14, 2007 at 7:31 am
No, I do not have the option to autoshrink checked. I have it set for Full recovery and I do backup the Trans log. For the Maint Plan the Optimization and Integ are run after the backup. I did try the reverse and still had the same problem.
Thanks,
John
March 14, 2007 at 9:45 am
If you constantly run the optimizations and it grows the log to 13GB, why bother shrinking? It's just going to grow again. Leave it at 13GB. If you truncate it after the optmizations, or run a backup, will keep it clear. It won't affect your tlog backups.
March 14, 2007 at 10:11 am
This is especially important concept to understand.
Shrinking the file is considered *bad*. The reason is because as the file grows again it may not have contiguous space to grow to on the file system. This means the file will be fragmented. Nothing kills performance like fragmented files.
It is best to allocate the file once and only once on a recently defragged file system. Schedule downtime if you must to get to this state.
If you find your log file grows bigger than you want, I recommend setting a maximum size so that it doesn't grow at all, it gets allocated and never changes size. Then back it up more often so that it does not fill.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply