File Growth Analysis via Default Trace

SQL Server continuously tracks certain critical events in the “default trace”. Among those events are the file growth events triggered when SQL Server automatically grows a database data or log file, or when a user manually expands a file.

The code below shows a simple method of seeing file growth events contained in the default trace.

To test this code, I created a simple, small database, and populated it with some data. Here’s the code to do that, which will drop any database you happen to have named ‘TestDB’:

After creating the test database, and growing it by inserting the test data, I ran the analysis code from the top of this post, which shows the following output:

File Growth Event Results

As you can see in the image above, the file growth events for this database, and it’s log files, are clearly outlined. There are four file growth events, each taking less than a second to complete; two for the data files, and two for the log files.

Let me know if you have any questions about this code in the comments below!

Ads by Google, Paying the Rent: