Q

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.

VIEW MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A.

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

 


MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A:

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

 BACKUP LOG databasename WITH TRUNCATE_ONLY

then

 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 first published in June 2005

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close