I'm running SQL Server 2000 and have set my database file to automatically grow as much as 10 percent when it needs the extra space. One of the databases has grown several times. How can I see when the growth occurred and how much the database grew each time?
I don't know of a simple way to get these answers after the files have grown. However, I agree that the information would be valuable, and I hope Microsoft will add a way to access the information in a future SQL Server release. Meanwhile, you can capture future file-growth operations by using one of two techniques. First, you can record a baseline, then periodically sample the contents of the sysfiles system table to look for changes. A baseline record will let you capture file growth as accurately as your sysfiles polling interval allows. Second, you can use SQL Server Profiler to track the Data File Auto Grow event of the Database class. SQL Server Books Online (BOL) says that this event "...indicates that the data file grew automatically. This event is not triggered if the data file is grown explicitly through ALTER DATABASE." You should be able to use this information to build a monitoring mechanism to capture the information you want.
