Hi,
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 23, 2012
Log file size increases over and over
Hi everybody,
I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:
SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.
So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions?
My free disk space is very low.
Thank you all,Since you do not backup the transaction log anyway, you can create a job to run on whatever schedule you choose to run code below:
BACKUP LOG DB_Name WITH TRUNCATE_ONLY
Lystra|||This command didn't work...
My log file size didn't decrease anything...
Does someone can help me with another suggestion?|||I'd try to shrink the database from SQL Enterprise Manager, or use the DBCC SHRINKDATABASE command from SQL Query Analyzer.
-PatP|||Before shrinking anything do DBCC OPENTRAN (db_name) to ensure there are no uncommitted transactions. Their presence wil;l prevent any attempt to shring log files.
It's also clear that you don't want to take advantage of Full recovery mode (I wonder why?), so set it to Simple.|||ok, now I got it!
It worked...
Thank you all...sql
I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:
SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.
So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions?
My free disk space is very low.
Thank you all,Since you do not backup the transaction log anyway, you can create a job to run on whatever schedule you choose to run code below:
BACKUP LOG DB_Name WITH TRUNCATE_ONLY
Lystra|||This command didn't work...
My log file size didn't decrease anything...
Does someone can help me with another suggestion?|||I'd try to shrink the database from SQL Enterprise Manager, or use the DBCC SHRINKDATABASE command from SQL Query Analyzer.
-PatP|||Before shrinking anything do DBCC OPENTRAN (db_name) to ensure there are no uncommitted transactions. Their presence wil;l prevent any attempt to shring log files.
It's also clear that you don't want to take advantage of Full recovery mode (I wonder why?), so set it to Simple.|||ok, now I got it!
It worked...
Thank you all...sql
Log File Size - Sudden Growth
I have a relatively small database that I use for development purposes (this
isn't the production database).
The .mdf is about 10 MB. The log file size - for over 1 year - has been
about 7MB in size. During the past couple of w
s the log file has grown to
over 15 MB in size. The amount of db-related development activity has
remained roughly constant over the past year. So I'm curious as to what
could be causing the log file to suddenly grow this large (small as it is in
the relative scheme of things).
Thanks!Felipe,
A couple of guesses: 1. Importing data 2. index creation or maintanence 3.
log backup didn't occur
HTH
Jerry
"Felipe" <aye@.bee.calm> wrote in message
news:OAgDoxGxFHA.3892@.TK2MSFTNGP12.phx.gbl...
>I have a relatively small database that I use for development purposes
>(this isn't the production database).
> The .mdf is about 10 MB. The log file size - for over 1 year - has been
> about 7MB in size. During the past couple of ws the log file has grown
> to over 15 MB in size. The amount of db-related development activity has
> remained roughly constant over the past year. So I'm curious as to what
> could be causing the log file to suddenly grow this large (small as it is
> in the relative scheme of things).
> Thanks!
>sql
isn't the production database).
The .mdf is about 10 MB. The log file size - for over 1 year - has been
about 7MB in size. During the past couple of w
s the log file has grown toover 15 MB in size. The amount of db-related development activity has
remained roughly constant over the past year. So I'm curious as to what
could be causing the log file to suddenly grow this large (small as it is in
the relative scheme of things).
Thanks!Felipe,
A couple of guesses: 1. Importing data 2. index creation or maintanence 3.
log backup didn't occur
HTH
Jerry
"Felipe" <aye@.bee.calm> wrote in message
news:OAgDoxGxFHA.3892@.TK2MSFTNGP12.phx.gbl...
>I have a relatively small database that I use for development purposes
>(this isn't the production database).
> The .mdf is about 10 MB. The log file size - for over 1 year - has been
> about 7MB in size. During the past couple of w
s the log file has grown> to over 15 MB in size. The amount of db-related development activity has
> remained roughly constant over the past year. So I'm curious as to what
> could be causing the log file to suddenly grow this large (small as it is
> in the relative scheme of things).
> Thanks!
>sql
Wednesday, March 21, 2012
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:
> > 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:
> > 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:
> > 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|||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:
> >
> > 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
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:
> > 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:
> > 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:
> > 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|||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:
> >
> > 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
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
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
Monday, March 19, 2012
Log File Full
My production database is very big in size and due to contious transactions, my log file becomes full frequently. I manually shrink log file when it becomes large. But i believe shrinking many times is not a good practise in terms of performance. So what should i do? Can anyone suggest a better approach.
How much frequency are you taking backup of transaction log ?
For example, if you are taking transaction log backup every 30 minutes, change it to every 15 minutes.
Thanks
Naras.
|||Yes, you rightly said that shrinking is not a good practice. You should control the growth of Transaction log and if space is not there in the current drive, you should shift TL to a better place. You can control the growth by taking backup of transaction log more frequently. When you shrink the transaciton log the backup chain breaks. so the first thing you do should be full backup. If you dont want point in time restore you can change the recovery model to simple which will restirct the gwoth of TL to a certain limit.
How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235.
Madhu
|||I take trn log backup every 2 hours. As i want point in time restore, i need to take tl back up atleast 2 hr.|||But if i don't shrink it grows beyond 2 GB in week. I need a poin in time restore so i had to keep full recovery model.|||what is the frequency of TL backup of this database.
Madhu
|||Every 2 hr TL Backup, daily Diff backup and weekly Full backup.|||You can still achieve a point in time restore, even if you take transaction log backup every 30/60/90/120 minutes.
Thanks
Naras.
|||schedule TL backup more frequently... say every 15 min... this is the better approach
Madhu
|||http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=263
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Addition to Madhu's references.
Subscribe to:
Posts (Atom)