Database File Changes

Data professionals around the globe are frequently finding themselves occupied with figuring out why and when a file (data or log) for a database has changed in size. Whether that change is a growth or shrink, or if the change was expected to happen or not.

I have written previously about the need to monitor these changes and how to do it more efficiently (there is even a fail-safe that occasionally works. As SQL Server improves, so does our ability to capture these types of events.

Given that SQL Server has undergone many enhancements, let’s take a look at the enhancements related to capturing the database file size changes.

Database File Related Events

If you read the articles mentioned previously, you will have encountered a previous article that include an Extended Event session that can be used for tracking file changes. That session is really good enough in most cases. One major change that I would suggest off the top is the asynchronous file target. While that target will work on all editions of SQL Server since 2008, the name of the target was changed in SQL Server 2012 to event_file. No big deal there. The XEM file is also no longer necessary, so that piece can just be wiped out.

That said, what else has changed? Let’s cover some deprecated events that may get you frustrated if you encounter them. The following events should be avoided because they will do nothing (a couple of them have been covered in this previous article).

• sqlserver.databases_log_growth – Databases log growth
• sqlserver.databases_log_file_size_changed – Databases log file size changed
• sqlserver.databases_data_file_size_changed – Databases data file size change
• sqlserver.auto_shrink_log – Auto shrink log ended.

Each of the preceding events have been replaced by a single event called database_file_size_change.

And then there is this one that I can’t seem to get to generate any events but it might possibly still be valid. In short, don’t be surprised one way or the other if it does something.

• sqlserver.auto_shrink_log_caught_exception – Auto shrink log caught an exception.

Great, we have some events we can avoid. Now let’s look at some events we should consider.

• sqlserver.database_file_size_change – 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.
• sqlserver.databases_shrink_data_movement – Databases shrink data movement
• sqlserver.databases_log_file_used_size_changed – Databases log file used size changed (this one gets noisy – use judiciously).
• sqlserver.databases_log_shrink – Databases log shrink

Now that we have some events that are viable as well as some events to avoid, let’s put a session together.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AuditFileSizev2' )
DROP EVENT SESSION AuditFileSizev2
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [AuditFileSizev2] ON SERVER
ADD EVENT sqlserver.database_file_size_change ( --good
ACTION ( sqlserver.database_id, sqlserver.session_id,
sqlserver.database_name, sqlserver.client_hostname, sqlserver.sql_text,
sqlserver.nt_username, sqlserver.username, sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.context_info,
sqlserver.client_connection_id )
),
ADD EVENT sqlserver.databases_shrink_data_movement ( --good
ACTION ( sqlserver.database_id, sqlserver.session_id,
sqlserver.database_name, sqlserver.client_hostname, sqlserver.sql_text,
sqlserver.nt_username, sqlserver.username, sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.context_info,
sqlserver.client_connection_id )
),
ADD EVENT sqlserver.databases_log_file_used_size_changed ( --good --this is a very chatty event and may be left off
ACTION ( sqlserver.database_id, sqlserver.session_id,
sqlserver.database_name, sqlserver.client_hostname, sqlserver.sql_text,
sqlserver.nt_username, sqlserver.username, sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.context_info,
sqlserver.client_connection_id )
),
ADD EVENT sqlserver.databases_log_shrink ( --good
ACTION ( sqlserver.database_id, sqlserver.session_id,
sqlserver.database_name, sqlserver.client_hostname, sqlserver.sql_text,
sqlserver.nt_username, sqlserver.username, sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.context_info,
sqlserver.client_connection_id )
)
ADD TARGET package0.event_file (  SET filename = 'C:DatabaseXEAuditFileSizev2.xel'
, max_file_size = 5
, max_rollover_files = 4 )
WITH ( MAX_MEMORY = 4 MB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, TRACK_CAUSALITY = ON
, MAX_DISPATCH_LATENCY = 1 SECONDS
, STARTUP_STATE = ON );
GO

All we need to do at this juncture is test the session.

Let’s drill into the database_file_size_change event and take a closer look.

There are a few things going on with this event. As previously mentioned, this event captures multiple different types of size related events. If you now look at the sections that I have circled in red, you will note that there is a flag that tracks if the event was an automatic size change. It doesn’t matter if it was a growth or shrink, both can be automatic. The way we can tell if it was an automatic shrink is due to the negative value in the size_change_kb field.

Next up, we have the green highlighted section. I have three distinct timestamps circled and separated by a dotted red line. Note the time difference between each of the groups. Each group of events is separated by 30 minutes. As it turns out, if you have Autoshrink enabled on your database, the timer is a 30 minute interval. Yes! Autoshrink just so happened to be enabled on this database – for testing purposes.

Wrapping it Up

Extended Events is a powerful tool with plenty of ease of use and flexibility. This flexibility allows the DBA to capably monitor the server for any issue be it small or large. This article demonstrated how to use Extended Events to monitor for file size changes and the same principles can be applied to any of the waits you may need to investigate. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

In addition, through the power of XE, we were able to capture a quick glimpse into some internals related to the database engine. In this case, we were able to see autoshrink in action and discover the frequency that it runs on when enabled.