Today, I’d like to consolidate 3 different areas of data file growth management from 3 of my favorite sources of best practices and information: SQL CAT, MS SQL TIPS and BOL.
The way that I boil this down for DBAs that I work with is this: for smaller, less critical databases, data file autogrow is a good practice to keep the database functioning and requires less care & feeding. For critical high-activity databases, you can keep autogrow set for data files, but manage the file usage and growth through tools like the UCP dashboard in SQL Server 2008 R2 or a central management tool like System Center. Here is the SQL CAT document with good practices to follow on data file storage in general, with a few good tips on managing data files on critical databases.
A more rudimentary (and complete) discussion on data file growth is on Microsoft’s site here. I point you to that because it is important to note that if you are using a tool that monitors your overall file system utilization, that does not tell you how full your data file is getting and when it will auto-grow. Keep an eye on the free space in the database with a script like the ones here or with Utility Control Point from SQL Server.
Lastly, the really, really awesome new feature in SQL Server 2008 R2 is policy-based management which allows you to apply a standard policy for data file autogrow across your ENTIRE SQL Server footprint. In addition, you can use it to do more than simply evaluate out-of-compliance SQL Server databases in terms of data file management. You can then APPLY that policy to each database. Here is a quick tutorial on how to do exactly that. ENJOY!