Summary

Σε αυτό το επεισόδιο SQL Server in Greek θα θούμε πως μπορούμε να κατανοήσουμε το τρόπο με τον οποίο μεγαλώνουν τα αρχεία τις κάθε database.

Video

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