AutoShrink Transaction Log File

[Bucfan1]

Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink" option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First – Backup Log <database name> With Truncate_Only
Second – DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.

[Simon Hayes]

"Bucfan1" <su*****@comcomsystems.com> wrote in message
news:c2**************************@posting.google.c om...

Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink" option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First - Backup Log <database name> With Truncate_Only
Second - DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.

It's not clear from your post how often you back up the transaction log. You
mention a database backup every night, but not a log backup. Only log
backups will free up space in the log - a full backup will not. When you
backup the log, the space will be reused for future transactions, or the log
will shrink if the space isn't needed. See "Transaction Log Backups" in
Books Online for more information.

Simon

[Erland Sommarskog]

[posted and mailed, please reply in news]

Bucfan1 (su*****@comcomsystems.com) writes:

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink" option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First – Backup Log <database name> With Truncate_Only
Second – DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.

To add to Simon's reponse: if you don't care about the transaction log,
and you don't need up-to-the-point recovery, but it is always to sufficient
from the nightly backup, your best option is simply to switch to
simple recovery.

In any case, I would advice against setting up some automatic shrink from
what you describe above. If you shrink the log, it will autogrow, and
autogrow takes resources and slows down the system.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Bucfan1" <su*****@comcomsystems.com> wrote in message
news:c2**************************@posting.google.c om...

Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
Remove what files?

I don't see a step here about actually performing a transaction log backup.

5.) The "AutoShrink" option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth.
This to me indicates that transaction log backups are not occurring.
When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First - Backup Log <database name> With Truncate_Only
Second - DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.

Note, the minute you do a backup log with truncate_only, you break the
transaction log chain and lose a lot of your recovery opportunities.

Also, avoid where possible shrinking the file, it can ultimately lead to
file level fragmentation.