Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation