Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server 2000 full transaction log

I have a SQL Server 2000 and I am having a problem with a full transaction log. The server settings are configured to automatically shrink and automatically grow database. How do I go about clearing or shrinking the transaction log for the database, and should auto shrink and auto grow database be disabled? I am not sure if I should run "BACKUP LOG databasename WITH TRUNCATE_ONLY" or "DBCC SHRINKFILE" with EMPTYFILE or both.


Enabling automatic growth for database and transaction log files is good and considered a best practice. You don't want these files to completely fill up because that'll stop transaction processing in its tracks.

On the other hand, enabling automatic shrinking of database is usually not good. The only time it's ever recommended is on small, single person applications intended for laptops and the like. (Remember that auto-growth and auto-shrink are extremely costly operations. So you want to avoid these operations on an enterprise database application.) In your situation, it sounds like auto-shrink is causing you to run out of space quite frequently. So I strongly encourage you to disable auto-shrink.

Once you've disable this feature, you should experience these problems less frequently. But it's no guarantee that you'll never experience these problems again.

I have a couple rules of thumb I use to improve performance and avoid those costly auto-growth operations. First, create a transaction log file that's large enough to avoid auto-growth. I usually create a log file that's 25% the size of the database, but never less than 100mb on an enterprise application. Second, make sure the transaction log file is on a separate physical drive (or RAID volume) than the database. This greatly improves I/O performance. Finally, if the log should ever fill up, manually clean it out using "BACKUP LOG databasename WITH TRUNCATE_ONLY". Don't use DBCC SHRINKFILE!

For more information, look at the following Microsoft Knowledge Base articles:

How to stop the transaction log of a SQL Server database from growing unexpectedly

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE

INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server



At the end of this article, you say not to use DBCC SHRINKFILE. Why?
—George M.


Would you please explain why not to use DBCC SHRINKFILE?

I have a 3GB database with a 1GB log file. The log file goes up to 2GB everyday. The maintenance plan never shrinks it back to its smaller size unless I do



 dbcc shrinkfile.

—Salam E.


There's nothing wrong with using DBCC SHRINKFILE everyone once in a blue moon to correct an abnormally growing transaction log file or data file. However, you're walking in worst practices territory if you regularly shrink files.

In a nutshell, file growth or shrinkage are two of the most I/O consumptive activities that can occur on SQL Server. You should design your applications to avoid frequent file growths followed by file shrinkage. If your log file grows to 2GB everyday, then you should either a) set the log file size at a permanent 2GB, or b) set frequent transaction log backups, say every hour, to dump the log data to disk and keep it small. The second option provides the added benefit of allowing much greater backup granularity up to a given hour.
—Kevin Kline, Monitoring/Administration Expert


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in June 2005

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.