Large Transaction Log Files - SQL 2000

The SQL 2000 transaction log backups for our customer's databases are
regularly quite large - often times almost as large as the database itself
despite the fact that we run transaction log backups nightly.
We're wondering if the size of the log backups is related to the SQL
optimization jobs we regularly run. How often is it appropriate to run an
optimization job? When is the best time to do the optimizations? (After a
full db backup? Before a full db backup? After a transaction log
backup?Before a transaction log backup?) Or does it really matter at all?
Our databases vary greatly in size from a few megabytes to a few gigabytes.
Being ecommerce website databases, a large percentage of the activity is
read activity (shoppers browsing the site) but there is write activity when
shoppers register, place an order etc. The number of transactions varies
widely from a transaction once a week to hundreds or thousands of
transactions per day depending on which customers database we are talking
about.
Thanks,
BradIt sounds like a lot of the Transaction Log activity may be related to the
optimization efforts.
Why do you feel the need to exercise optimization efforts out of band with
the backup schedule?
Depending upon write activity, and types of indexes, padding, etc.,
optimization may be occurring much too often.
If you wanted to provide more details about your optimization and backup
schedules, we may be able to give you more directed assistance.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> The SQL 2000 transaction log backups for our customer's databases are
> regularly quite large - often times almost as large as the database itself
> despite the fact that we run transaction log backups nightly.
> We're wondering if the size of the log backups is related to the SQL
> optimization jobs we regularly run. How often is it appropriate to run an
> optimization job? When is the best time to do the optimizations? (After a
> full db backup? Before a full db backup? After a transaction log
> backup?Before a transaction log backup?) Or does it really matter at all?
> Our databases vary greatly in size from a few megabytes to a few
> gigabytes. Being ecommerce website databases, a large percentage of the
> activity is read activity (shoppers browsing the site) but there is write
> activity when shoppers register, place an order etc. The number of
> transactions varies widely from a transaction once a week to hundreds or
> thousands of transactions per day depending on which customers database we
> are talking about.
> Thanks,
> Brad
>|||> We're wondering if the size of the log backups is related to the SQL optimization jobs we
> regularly run.
Likely. It depends on what you actually mean by "optimization". If you mean index rebuilds, then all
data that you "shuffle" will be logged. This can easily be almost same as db size. It is also likely
that you do lots if optimization when you don't have to. Reading
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx is a good start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad Baker" <brad@.nospam.nospam> wrote in message news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> The SQL 2000 transaction log backups for our customer's databases are regularly quite large -
> often times almost as large as the database itself despite the fact that we run transaction log
> backups nightly.
> We're wondering if the size of the log backups is related to the SQL optimization jobs we
> regularly run. How often is it appropriate to run an optimization job? When is the best time to do
> the optimizations? (After a full db backup? Before a full db backup? After a transaction log
> backup?Before a transaction log backup?) Or does it really matter at all?
> Our databases vary greatly in size from a few megabytes to a few gigabytes. Being ecommerce
> website databases, a large percentage of the activity is read activity (shoppers browsing the
> site) but there is write activity when shoppers register, place an order etc. The number of
> transactions varies widely from a transaction once a week to hundreds or thousands of transactions
> per day depending on which customers database we are talking about.
> Thanks,
> Brad
>|||Thanks for the information. Here is how we have our SQL 2000 Maintenance
Plan Setup:
[Optimization]
[x] Reorganize data and index pages
( ) Reorganize pages with the original amount of free space
(*) Change Free Space per page percentage to: 10%
[ ] Update the statistics used by the query optimizer
Percentage of database to sample: (grayed out)
[ ] Remove unused space from the database files
( ) Shrink database when it grows beyond: (grayed out)
( ) Amount of free space to remain after shrink (grayed out)
Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
[Integrity Check]
[x] Check database integrity
(*) Include indexes
[ ] Attempt to repair any minor problems
( ) Excluded indexes
[ ] Perform these tests before backing up the database or transaction log.
Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
[Complete Backup]
[x] Backup the database as part of the maintenance plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: BAK
Occurs every 1 week(s) on Sunday, at 2:00:00 AM
[Transaction Log Backup]
[x] Backup the transaction log of the database as part of the maintenance
plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: TRN
Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
The reason for limiting transaction log backups to the night was that we
found they were negatively impacting website performance.
Hopefully that makes sense but please let me know if you have questions.
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
> It sounds like a lot of the Transaction Log activity may be related to the
> optimization efforts.
> Why do you feel the need to exercise optimization efforts out of band with
> the backup schedule?
> Depending upon write activity, and types of indexes, padding, etc.,
> optimization may be occurring much too often.
> If you wanted to provide more details about your optimization and backup
> schedules, we may be able to give you more directed assistance.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run an
>> optimization job? When is the best time to do the optimizations? (After a
>> full db backup? Before a full db backup? After a transaction log
>> backup?Before a transaction log backup?) Or does it really matter at all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is write
>> activity when shoppers register, place an order etc. The number of
>> transactions varies widely from a transaction once a week to hundreds or
>> thousands of transactions per day depending on which customers database
>> we are talking about.
>> Thanks,
>> Brad
>|||From your statement that TLog backups impact the website performance, it
sounds like the SQL Server is running on the same box as the Web Server AND
it must also be an 'underpowered' server. (I've often seen TLog backups on
multiGB databases having lots of activity take only seconds when done
hourly -and the users never realized anything was different.)
For your current schedule, I would make sure that there is NO TLog backup on
Sunday -it is most likely is happening before the optimization and FULL
Backup.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Thanks for the information. Here is how we have our SQL 2000 Maintenance
> Plan Setup:
>
> [Optimization]
> [x] Reorganize data and index pages
> ( ) Reorganize pages with the original amount of free space
> (*) Change Free Space per page percentage to: 10%
>
> [ ] Update the statistics used by the query optimizer
> Percentage of database to sample: (grayed out)
>
> [ ] Remove unused space from the database files
> ( ) Shrink database when it grows beyond: (grayed out)
> ( ) Amount of free space to remain after shrink (grayed out)
>
> Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
>
>
>
> [Integrity Check]
> [x] Check database integrity
> (*) Include indexes
> [ ] Attempt to repair any minor problems
> ( ) Excluded indexes
>
> [ ] Perform these tests before backing up the database or transaction log.
>
> Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
>
>
>
> [Complete Backup]
> [x] Backup the database as part of the maintenance plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: BAK
>
> Occurs every 1 week(s) on Sunday, at 2:00:00 AM
>
>
> [Transaction Log Backup]
> [x] Backup the transaction log of the database as part of the maintenance
> plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: TRN
>
> Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
>
>
> The reason for limiting transaction log backups to the night was that we
> found they were negatively impacting website performance.
>
> Hopefully that makes sense but please let me know if you have questions.
>
> Thanks,
> Brad
>
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> It sounds like a lot of the Transaction Log activity may be related to
>> the optimization efforts.
>> Why do you feel the need to exercise optimization efforts out of band
>> with the backup schedule?
>> Depending upon write activity, and types of indexes, padding, etc.,
>> optimization may be occurring much too often.
>> If you wanted to provide more details about your optimization and backup
>> schedules, we may be able to give you more directed assistance.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Brad Baker" <brad@.nospam.nospam> wrote in message
>> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run
>> an optimization job? When is the best time to do the optimizations?
>> (After a full db backup? Before a full db backup? After a transaction
>> log backup?Before a transaction log backup?) Or does it really matter at
>> all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is
>> write activity when shoppers register, place an order etc. The number of
>> transactions varies widely from a transaction once a week to hundreds or
>> thousands of transactions per day depending on which customers database
>> we are talking about.
>> Thanks,
>> Brad
>>
>|||Does you maintenance plan include more than one database?
I ask, because we are having the same problem and our maintenance plan
specifies 3 databases - where the 1st log file looks fine and the 2nd & 3rd
are quite large.
I just made a post on the subject.|||We have 6 maintenance plans - all of which contain about 60 databases each.
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:EF1DFA32-E412-4A9D-850E-68783CF8BDA0@.microsoft.com...
> Does you maintenance plan include more than one database?
> I ask, because we are having the same problem and our maintenance plan
> specifies 3 databases - where the 1st log file looks fine and the 2nd &
> 3rd
> are quite large.
> I just made a post on the subject.|||We have several DB servers all experiencing this problem. They are all
dedicated SQL servers though - no other applications running on them.
The servers are running dual xeon processors with 8GB of RAM. Some of the
servers have a handful of very large databases and other servers have a lot
(100-200) small databases. That's not to say the server isn't underpowered
for the load we are placing on it - sometimes the load is quite high and
other times its nominal.
When the Tlog backups were running we would see ASP errors involving SQL
timeouts as well as short disruptions in service.. so that's why we decided
we probably out to limit tlog backups to night.
I'm going to try disabling transaction log backups on Sunday and see if that
improves things. If you have any other ideas please let me know :)
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uuYyNXxEHHA.4508@.TK2MSFTNGP02.phx.gbl...
> From your statement that TLog backups impact the website performance, it
> sounds like the SQL Server is running on the same box as the Web Server
> AND it must also be an 'underpowered' server. (I've often seen TLog
> backups on multiGB databases having lots of activity take only seconds
> when done hourly -and the users never realized anything was different.)
> For your current schedule, I would make sure that there is NO TLog backup
> on Sunday -it is most likely is happening before the optimization and FULL
> Backup.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the information. Here is how we have our SQL 2000 Maintenance
>> Plan Setup:
>>
>> [Optimization]
>> [x] Reorganize data and index pages
>> ( ) Reorganize pages with the original amount of free space
>> (*) Change Free Space per page percentage to: 10%
>>
>> [ ] Update the statistics used by the query optimizer
>> Percentage of database to sample: (grayed out)
>>
>> [ ] Remove unused space from the database files
>> ( ) Shrink database when it grows beyond: (grayed out)
>> ( ) Amount of free space to remain after shrink (grayed out)
>>
>> Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
>>
>>
>>
>> [Integrity Check]
>> [x] Check database integrity
>> (*) Include indexes
>> [ ] Attempt to repair any minor problems
>> ( ) Excluded indexes
>>
>> [ ] Perform these tests before backing up the database or transaction
>> log.
>>
>> Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
>>
>>
>>
>> [Complete Backup]
>> [x] Backup the database as part of the maintenance plan
>> [x] Verify the integrity of the backup upon completion
>>
>> ( ) Tape (grayed out)
>> ( ) Disk
>> ( ) Use the default backup directory
>> (*) Use this directory (backup directory)
>>
>> [x] Create a sub-directory for each database
>> [x] Remove files older than 2 weeks
>>
>> Backup extension: BAK
>>
>> Occurs every 1 week(s) on Sunday, at 2:00:00 AM
>>
>>
>> [Transaction Log Backup]
>> [x] Backup the transaction log of the database as part of the maintenance
>> plan
>> [x] Verify the integrity of the backup upon completion
>>
>> ( ) Tape (grayed out)
>> ( ) Disk
>> ( ) Use the default backup directory
>> (*) Use this directory (backup directory)
>>
>> [x] Create a sub-directory for each database
>> [x] Remove files older than 2 weeks
>>
>> Backup extension: TRN
>>
>> Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59
>> AM.
>>
>>
>> The reason for limiting transaction log backups to the night was that we
>> found they were negatively impacting website performance.
>>
>> Hopefully that makes sense but please let me know if you have questions.
>>
>> Thanks,
>> Brad
>>
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> It sounds like a lot of the Transaction Log activity may be related to
>> the optimization efforts.
>> Why do you feel the need to exercise optimization efforts out of band
>> with the backup schedule?
>> Depending upon write activity, and types of indexes, padding, etc.,
>> optimization may be occurring much too often.
>> If you wanted to provide more details about your optimization and backup
>> schedules, we may be able to give you more directed assistance.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Brad Baker" <brad@.nospam.nospam> wrote in message
>> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run
>> an optimization job? When is the best time to do the optimizations?
>> (After a full db backup? Before a full db backup? After a transaction
>> log backup?Before a transaction log backup?) Or does it really matter
>> at all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is
>> write activity when shoppers register, place an order etc. The number
>> of transactions varies widely from a transaction once a week to
>> hundreds or thousands of transactions per day depending on which
>> customers database we are talking about.
>> Thanks,
>> Brad
>>
>>
>