sqlschool.gr logo

articles

Articles of SQLschool.gr Team

How database files are growing?

Antonios Chatzipavlis
Saturday 06 October 2018





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

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.