go backarticles

Articles of SQLschool.gr Team

How database files are growing?

Antonios Chatzipavlis





Presentation Code

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;


Power BI file

Download Power BI file

Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


PASS chapter logo

The Official PASS Local Group for Greece

1429 33 592 27 39 1308
sql school greece logo
© 2010-2019 All rights reserved