Rozrost tempdb – Extended Events + podgląd via TSQL

Michał Pawlikowski

Skrypt dzięki uprzejmości Igora Yaremenko 🙂

-- create event session
CREATE EVENT SESSION [Database_Growth_Watchdog]
    ON SERVER
    ADD EVENT sqlserver.database_file_size_change
    ( ACTION ( sqlserver.client_app_name ,
               sqlserver.client_hostname ,
               sqlserver.database_name ,
               sqlserver.session_nt_username ,
               sqlserver.sql_text ,
               sqlserver.username )
     WHERE ( [database_id] = ( 2 )))
    ADD TARGET package0.event_file
    ( SET filename = N'D:\Temp\Database_Growth_Wathdog.xel', max_file_size = ( 10 ))
    WITH ( MAX_MEMORY = 4096KB ,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
           MAX_DISPATCH_LATENCY = 1 SECONDS ,
           MAX_EVENT_SIZE = 0KB ,
           MEMORY_PARTITION_MODE = NONE ,
           TRACK_CAUSALITY = OFF ,
           STARTUP_STATE = ON );
GO


-- parsing event session log
DECLARE @TraceFileLocation NVARCHAR(255) = N'D:\Temp\Database_Growth_Wathdog*.xel';
WITH FileSizeChangedEvent
AS ( SELECT object_name Event ,
            CONVERT(XML, event_data) Data
     FROM   sys.fn_xe_file_target_read_file(@TraceFileLocation, NULL, NULL, NULL) ) ,
     FileSizeChangedEventParsed
AS ( SELECT Data.value('(/event/@timestamp)[1]', 'DATETIME') EventTime ,
            Data.query('/event/action[@name=''client_hostname'']/value').value('.', 'VARCHAR(MAX)') ClientHostname ,
            Data.query('/event/action[@name=''username'']/value').value('.', 'VARCHAR(MAX)') ClientUsername ,
            Data.query('/event/action[@name=''client_app_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppName ,
            Data.query('/event/action[@name=''database_name'']/value').value('.', 'VARCHAR(MAX)') ClientAppDBName ,
            Data.query('/event/data[@name=''database_id'']/value').value('.', 'INT') SystemDatabaseId ,
            Data.query('/event/data[@name=''file_name'']/value').value('.', 'VARCHAR(MAX)') SystemDatabaseFileName ,
            Data.query('/event/data[@name=''file_type'']/text').value('.', 'VARCHAR(MAX)') SystemDatabaseFileType ,
            Data.query('/event/data[@name=''is_automatic'']/text').value('.', 'VARCHAR(MAX)') SystemIsAutomaticGrowth ,
            Data.query('/event/data[@name=''size_change_kb'']/value').value('.', 'BIGINT') GrowthInKB ,
            Data.query('/event/data[@name=''total_size_kb'']/value').value('.', 'BIGINT') TotalSizeInKB ,
            Data.query('/event/data[@name=''duration'']/value').value('.', 'BIGINT') SystemDuration ,
            Data.query('/event/action[@name=''sql_text'']/value').value('.', 'VARCHAR(MAX)') SQLCommandText
     FROM   FileSizeChangedEvent )
SELECT *
FROM   FileSizeChangedEventParsed;

 

Dodaj komentarz