[posted and mailed, please reply in news]
Bucfan1 (support@comcomsystems.com) writes:[color=blue]
> 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.[/color]
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,
sommar@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
