A quick post that is hopefully useful, I wanted a quick way to find the time, size of the database file size change and who caused it.

I went down the extended events route using sqlserver.database_file_size_change event. By the way I am no Extended Events expert, I write a lot via trial and error I am trying to wean off Profiler.

CREATE EVENT SESSION [GetInfo] ON SERVER
ADD EVENT sqlserver.database_file_size_change(
   ACTION(package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username))
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (STARTUP_STATE=ON)
GO

Ideally you probably want to write it out to a XEL file but the above shows the ring buffer as a target.

I suggest XQuery because in the above format it can be awkward to read.

DECLARE @ShredMe XML;
SELECT @ShredMe = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
    ON t.event_session_address = s.address
WHERE s.name = N'GetInfo';

SELECT
QP.value('(data[@name="database_id"]/value)[1]', 'INT') as [DatabaseID],
QP.value('(action[@name="nt_username"]/value)[1]', 'varchar(50)') as [User],
QP.value('(action[@name="client_app_name"]/value)[1]', 'varchar(100)') as [App],
QP.value('(action[@name="database_name"]/value)[1]', 'varchar(50)') as [Database Name],
QP.value('(data[@name="file_type"]/text)[1]', 'varchar(50)') as [File Growth Type],
QP.value('(@timestamp)[1]', 'datetime2') AS [timestamp of growth],
QP.value('(data[@name="size_change_kb"]/value)[1]', 'BIGINT') as [size_change_kb]
FROM @ShredMe.nodes('RingBufferTarget/event[@name=''database_file_size_change'']') AS q(QP);