videos
video collection of sqlschool.gr
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