|
Home > Archive > MS SQL Server > March 2006 > SQL TXN Log Files Size
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL TXN Log Files Size
|
|
| John Williams 2006-03-31, 11:23 am |
| If you have a 'full' recovery option for a user database. And the transaction
log is set to autogrow...Does the file ever shrink by itself? I know the used
portion goes down. But does the file size on the hard drive go down once it
has grown on its own after log backups or sql restart?
If we have a weekly process that grows the file from 100 MB to 1 GB, but
then after wards during normal processing file only uses needs 100 MB - 200
MB of ther 1 GB during the week. Is it best practice to 'shrink' the log
file after the weekly maintenance?
| |
| Linchi Shea 2006-03-31, 1:23 pm |
| If you don't shrink it or set it to shrink, the file size will not shrink.
1GB is not a huge amount. I'd just leave it there. What do you gain when you
shrink it back to 100MB? Nothing. In fact, if I know the tran log needs 1 GB,
I'd pre-allocate more than 1GB to it just in case.
Linchi
"John Williams" wrote:
> If you have a 'full' recovery option for a user database. And the transaction
> log is set to autogrow...Does the file ever shrink by itself? I know the used
> portion goes down. But does the file size on the hard drive go down once it
> has grown on its own after log backups or sql restart?
>
> If we have a weekly process that grows the file from 100 MB to 1 GB, but
> then after wards during normal processing file only uses needs 100 MB - 200
> MB of ther 1 GB during the week. Is it best practice to 'shrink' the log
> file after the weekly maintenance?
| |
| John Williams 2006-03-31, 1:23 pm |
| Linchi,
Thanks for the response...
Actually I was using made up data.. in my case the log file is growing to 22
GB... I do not know if I shrink it, if some type of prcoessing causes it to
grow back to that size...
If there was a weekend maintenance that caused it to grow, and then during
normal processing, it only used a small %, is it better to shrink?
I am not sure at this point if the next weekend it would just grow again...
"Linchi Shea" wrote:
[color=darkred]
> If you don't shrink it or set it to shrink, the file size will not shrink.
> 1GB is not a huge amount. I'd just leave it there. What do you gain when you
> shrink it back to 100MB? Nothing. In fact, if I know the tran log needs 1 GB,
> I'd pre-allocate more than 1GB to it just in case.
>
> Linchi
>
> "John Williams" wrote:
>
| |
| Linchi Shea 2006-03-31, 1:23 pm |
| If you consider 22GB too excessive (in most cases it is), you may want to
check the batch process to see whether you can adjust its transaction batch
size so that it doesn't cause the tran log to grow that big.
I know sometimes there is not much you can do. We actually have a case where
we had to switch the recovery mode to simple for the weekend batch process to
contain the tran log size, and then switch the recovery mode to full and do a
full database backup when the batch process is done. This may or may not be
something you should consider.
Linchi
"John Williams" wrote:
[color=darkred]
> Linchi,
> Thanks for the response...
> Actually I was using made up data.. in my case the log file is growing to 22
> GB... I do not know if I shrink it, if some type of prcoessing causes it to
> grow back to that size...
> If there was a weekend maintenance that caused it to grow, and then during
> normal processing, it only used a small %, is it better to shrink?
> I am not sure at this point if the next weekend it would just grow again...
>
>
> "Linchi Shea" wrote:
>
| |
| Andrew J. Kelly 2006-03-31, 8:23 pm |
| The growth is most likely due to reindexing that is occurring on the
weekend. If you don't issue log backups at regular enough intervals it may
need to hold as much data as is in the database if all the tables are
reindexed.
--
Andrew J. Kelly SQL MVP
"John Williams" < JohnWilliams@discuss
ions.microsoft.com> wrote in message
news:F27267C3-DB7B-46B3-8DE6- 9FD6824B822F@microso
ft.com...[color=darkred]
> Linchi,
> Thanks for the response...
> Actually I was using made up data.. in my case the log file is growing to
> 22
> GB... I do not know if I shrink it, if some type of prcoessing causes it
> to
> grow back to that size...
> If there was a weekend maintenance that caused it to grow, and then during
> normal processing, it only used a small %, is it better to shrink?
> I am not sure at this point if the next weekend it would just grow
> again...
>
>
> "Linchi Shea" wrote:
>
|
|
|
|
|