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:
MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A:
At the end of this article, you say not to use DBCC SHRINKFILE. Why?
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
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.
Related Q&A from Kevin Kline, Director of SQL Server Solutions, Quest Software
Monitoring and Administration expert Kevin Kline discusses the obstacles of database administration when a publisher resides on a different domain.continue reading
A common SQL Mail error is addressed by monitoring and administration expert Kevin Kline.continue reading
Monitoring and Administration expert Kevin Kline discusses the installation of SQL Server client tools on Windows XP Pro.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.