I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to get a gauge of the amount of external fragmentation for each of the DB files, can I get from metadata how many times a DB file has been modified in size (either by autogrow or manually)? To clarify, can I get from metadata the history of DB file modifications for the life of the DB, not just since instance restart?
You can get autogrowth events information from the default trace if it is enabled:
You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow events capture. To see if you have the default trace enabled on that instance, you can do the following:
Note: this is an advanced configuration option, so
Here is a quick sample query to get these events:
And you can get