In honor of TSQL Tuesday for September, the mother post is with Grant Fritchey – TSQL 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.
|
1 2 3 4 5 6 7 8 |
CREATE EVENT SESSION [FileSizeAutogrow] ON SERVER ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1) ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)), ADD EVENT sqlserver.databases_log_file_size_changed( ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)) ADD TARGET package0.event_file(SET filename=N'FileSizeAutogrow.xel',max_file_size=(500),max_rollover_files=(10)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO |
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.
Pingback: Capturing Autogrowth Events in SQL Server – Curated SQL