TSQL Tuesday – One of my favorite Extended Events

In honor of TSQL Tuesday for September, the mother post is with Grant FritcheyTSQL Tuesday 166.

I wanted to share one of the Extended Events I always put on a server when I am in charge of it. It has to do with File growths and captures some important things for me. Before you say that it is in the system_health extended events session, I know that it is there. I have had system_health sessions cycle pretty fast and there are a lot of other events in that trace, so I decided to make my own for just that specific thing so that I can archive the sessions and keep the disk clean as well as pull this information into a table and analyze data in a tabular way instead of mining XE files.

Here it is for those that would like to use it as well.

You will notice that the events that are captured are sqlserver.database_file_size_change and sqlserver.databases_log_file_size_changed (yes, it is weird to have the names be so different. The first one has no (s) on database_file_size_change and the second there is no (d) on change. On the log file size change it is database(s) plural and change(d). Not sure what the significance of that is, but it is not intuitive to find in XE. These events are available from SQL 2012 and onwards. They do not exist in SQL 2008R2.

What do I get out of this?

Notice that in the first graphic I have marked it up. You get a lot of things out of this data.
* Database Name / Filename
* File Type
* Duration of the growth
* Size change in KB

I carry a mantra that if you keep all files from growing (proactive maintenance of files) then you can grow them automatedly but at a time when it is more conducive to grow it and not in the middle of a great big transaction. Automation is how you get the most out of your day without the heavy lifting that many DBAs still have as part of their DBA careers.

Happy TSQL Tuesday from the Ducks on my side of the pond.

This Post Has One Comment

Leave a Reply