DROP EVENT SESSION [DB Size Trace] ON SERVER 
GO

CREATE EVENT SESSION [DB Size Trace] ON SERVER 
ADD EVENT sqlserver.database_file_size_change
ADD TARGET package0.event_file(SET filename=N'DB Size Trace')
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

ALTER EVENT SESSION [DB Size Trace] ON SERVER STATE=START
GO


-- query XE

with dl as 
(
       select cast(event_data as xml) as xml_event_data
       from   sys.fn_xe_file_target_read_file ( 'DB Size Trace*.xel',null,null,null ) 
       where object_name=N'database_file_size_change'
)
select  
        xml_event_data.value(N'(event/data[@name="database_id"]/value)[1]', N'int') as database_id
    ,   DB_NAME(xml_event_data.value(N'(event/data[@name="database_id"]/value)[1]', N'int')) as database_name
    ,   xml_event_data.value(N'(event/@timestamp)[1]', N'datetime') as operation_date
    ,   xml_event_data.value(N'(event/data[@name="file_name"]/value)[1]', N'nvarchar(256)') as file_name
    ,   xml_event_data.value(N'(event/data[@name="file_id"]/value)[1]', N'int') as file_id
    ,   xml_event_data.value(N'(event/data[@name="file_type"]/value)[1]', N'int') as file_type
    ,   xml_event_data.value(N'(event/data[@name="file_type"]/text)[1]', N'nvarchar(50)') as file_type_desc
    ,   xml_event_data.value(N'(event/data[@name="is_automatic"]/value)[1]', N'bit') as is_automatic
    ,   xml_event_data.value(N'(event/data[@name="duration"]/value)[1]', N'bigint')/1000 as duration_ms
    ,   cast ( xml_event_data.value(N'(event/data[@name="total_size_kb"]/value)[1]', N'bigint') / 1024.0 as decimal(18,2)) as total_size_mb
    ,   cast ( xml_event_data.value(N'(event/data[@name="size_change_kb"]/value)[1]', N'bigint') / 1024.0 as decimal(18,2)) as size_change_md
from dl
order by operation_date asc;