One of the things that a DBA tries to avoid/prevent are automatic file growths in data and log files. We generally like to plan for these things and size the data and log files ourselves to prevent SQL Server from having to do it on its own. But sometimes you can’t help it. Sometimes tempdb grows unexpectedly overnight, or someone does a large data import into a user database.
This can be frustrating, especially when you don’t know what T-SQL statement caused the file growth, or what user ran the script. For that reason, I have the following Extended Event running on all of my SQL Servers.
|
1 2 3 4 5 6 7 8 |
CREATE EVENT SESSION [DB Size Tracking] ON SERVER ADD EVENT sqlserver.database_file_size_change( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)) ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvent\DB Size Tracking.xel') 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 |
The event itself is pretty light weight, it uses the database_file_size_change event to track size changes of the data and log files and records what caused those changes, including the user and T-SQL script used.
However, if we take a look at the database_file_size_change event that we are using, we will find that it doesn’t just apply to file growth. Here is the description of the database_file_size_change event taken from the Extended Event wizard:
Occurs when any of the data or log files for a database changes size. Use this event to monitor changes to the size of database files.
It tracks any changes in size to the data and log files. This means that the the above Extended Event session will also track any SHRINKFILE operations on a database’s data and log files. This can come in handy if you have a rouge developer who likes to shrink log files when he things no one is looking. (Yeah, I had someone do that once.)
Even though I don’t use Extended Events as much as I probably should, in cases like this I’m glad they are around and certainly see the benefit in using them.