1

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))
Andrew
  • 11
  • 1

1 Answers1

0

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!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • That was my initial theory, that the index took up existing space, and subsequent transactions caused the log file to grow. I appreciate the feedback. – Andrew Jun 28 '21 at 13:37