Extended Events - Not showing log file growth

233 Views Asked by At

We have a standard extended event session to collect database and log file growth running on our SQL servers. I was creating an index on a large table, knowing it might fill up the transaction log. It did, however the DBA that went to investigate why the log drive was out of room, couldn't see the index build as the culprit. It was showing every other transaction but the index build. Does anyone know the reason behind this? I killed the index build as soon as I realized there was a problem, and it released all of the space. I would like to know the WHY behind this.

This server is running SQL 2016 CU 15 - Enterprise edition.

Here is the query I was running:

CREATE NONCLUSTERED INDEX [ix_myIndex]
    ON [dbo].[MyTable]([MyField] ASC)
    WITH(PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=ON, DROP_EXISTING=OFF, ONLINE=ON, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON);

Below is the event we have for capturing log file growth:

 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))
1

There are 1 best solutions below

1
Ben Thul On

Let me extend an analogy. Let's say you had a swimming pool that was mostly filled with water (because who likes to swim in an empty pool?). Now it starts to rain and the swimming pool overflows! Would you say it was the rain that caused it to overflow or the fact that it was mostly full when it started to rain?

The extended event is capturing the rain event from my example. And it's technically right!