Log File Management

I am working with a client on a production OLTP database using Full
Recovery model. I normally work in datawarehouse environments with
Simple Recovery turned on. They are performing nightly Full Backups and
hourly log backups Monday through Friday. Sunday night is a normal full
backup followed by a log backup and shrinking the log file. Everyweek
the log grows from 200MB on Monday Morning to 12GB on Friday. I have
already recommended not shrinking the log files on Sunday nights as it
only introduces overhead each day during peak hours while the database
expands the log files it previously shrank. My question is why does
this file continue to grow throughout the week? I would expect that
after each log file backup, the log segments that have been backed up
should become available to be reused. I don't see that they are issuing
a checkpoint, and I'm wondering if that is the issue. Can anyone shed
light on this. The information I have found in BOL and online would
indicate that my understanding is correct and the log files should be
being reused after the backup. I just want to be sure before I go
searching for issues with open transactions, etc... Thanks for any and
all help...
Kevinkevin.karlin@.gmail.com wrote:
> I am working with a client on a production OLTP database using Full
> Recovery model. I normally work in datawarehouse environments with
> Simple Recovery turned on. They are performing nightly Full Backups and
> hourly log backups Monday through Friday. Sunday night is a normal full
> backup followed by a log backup and shrinking the log file. Everyweek
> the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> already recommended not shrinking the log files on Sunday nights as it
> only introduces overhead each day during peak hours while the database
> expands the log files it previously shrank. My question is why does
> this file continue to grow throughout the week? I would expect that
> after each log file backup, the log segments that have been backed up
> should become available to be reused. I don't see that they are issuing
> a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> light on this. The information I have found in BOL and online would
> indicate that my understanding is correct and the log files should be
> being reused after the backup. I just want to be sure before I go
> searching for issues with open transactions, etc... Thanks for any and
> all help...
> Kevin
>
Are they doing index rebuilds or some sort of importing during the week?
The log backups will flush out any committed transactions. Reindexing
(or index defragging) generates a LOT of transactional activity.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||kevin.karlin@.gmail.com wrote:
> The only thing that doesn't appear to be happening that I normally
> implement on my datawarehouse boxes is issuing a checkpoint. I know
> that has to be done when you're in Simple Recovery mode, but I don't
> find reference to it being required when in Full Recovery mode. Could
> that be the problem?
>
Where are you getting the notion that you have to issue checkpoints?
Those should happen automatically in Simple mode. In the other modes,
backing up the transaction log performs the truncation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It seems I wasn't entirely clear in my second post. I normally work in
a DW environment not an OLTP environment. We don't have a need for
anything but Simple Recovery. The box that is having the problem is an
OLTP database using Full Recovery. I know that in Simple mode the
server will issue checkpoints automatically. However, my experience has
been that issuing a checkpoint right before a log truncation ensured
you got the most space released. This goes way back to version 6.5 as I
recall, so it may have become unnecessary at some point, but it is
still a habit. As far as the other recovery modes go - that was my
question, I couldn't find any documentation that said a checkpoint was
necessary in Full Recovery mode, but I wanted to be sure that that
wasn't the issue. At this point it seems that my understanding of what
should be happening is correct, now I need to find out what is
preventing the log backups from freeing the space.
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Where are you getting the notion that you have to issue checkpoints?
> Those should happen automatically in Simple mode. In the other modes,
> backing up the transaction log performs the truncation.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com