How can I find out when a database has auto-grown? I'd like to know how often a certain database has automatically grown without any DBA intervention and, if possible, which files have auto-grown and to what size. With this information, I could more easily decide whether to let a database auto-grow and how much to let it grow.
Many DBAs ask this question. SQL Server 2000 doesn't have a built-in function for retrieving this information. However, you can determine this information fairly easily by setting up a baseline of file size per database and remembering to re-baseline after any manual growth operation. The following command will give you the total number of 8K data pages in a database:
SELECT GETDATE(), SUM(size)
FROM\[dbname].dbo.sysfiles
WHERE status & 0x40 <> 0x40 —
/* Use status flag to exclude log files.*/
If you save the results of this query to a baseline table, then periodically compare these results with the current values, you'll see the growth. If you remember to re-baseline after each manual growth operation, you should see only autogrow operations. To track individual file growth, you can modify the query to look something like this:
SELECT GETDATE(),name,size
FROM pessimistic.dbo.sysfiles
WHERE status & 0x40 <> 0x40
And if you track growth over time (i.e., maintain multiple rows in your baseline table, and perhaps build an Analysis Services cube to track the data), you can capture the velocity of growth. In SQL Server 2005, you'll be able to build logic to capture the Data_File_Auto_Grow and Audit_Database_* (to be defined) trace events as notifications and to process the notifications, maybe by adding the new size to the baseline table.