autogrowth

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;

-- 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 );

-- 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;

Znalezione na stronie The SQL Guy by Norm Enger

USE [master];
GO

BEGIN TRY
    IF
    (
        SELECT CONVERT(INT, value_in_use)
        FROM sys.configurations
        WHERE name = 'default trace enabled'
    ) = 1
    BEGIN
        DECLARE @curr_tracefilename VARCHAR(500);
        DECLARE @base_tracefilename VARCHAR(500);
        DECLARE @indx INT;

        SELECT @curr_tracefilename = path
        FROM sys.traces
        WHERE is_default = 1;
        SET @curr_tracefilename = REVERSE(@curr_tracefilename);
        SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
        SET @curr_tracefilename = REVERSE(@curr_tracefilename);
        SET @base_tracefilename = LEFT(@curr_tracefilename, LEN(@curr_tracefilename) - @indx) + '\log.trc';
        SELECT
            --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
            ServerName AS [SQL_Instance],
            --CONVERT(INT, EventClass) AS EventClass,
            DatabaseName AS [Database_Name],
            Filename AS [Logical_File_Name],
            (Duration / 1000) AS [Duration_MS],
            CONVERT(VARCHAR(50), StartTime, 100) AS [Start_Time],
            --EndTime,
            CAST((IntegerData * 8.0 / 1024) AS DECIMAL(19, 2)) AS [Change_In_Size_MB]
        FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
        WHERE EventClass >= 92
              AND EventClass <= 95
        --AND ServerName = @@SERVERNAME
        --AND DatabaseName = 'myDBName'  
        ORDER BY DatabaseName,
                 StartTime DESC;
    END;
    ELSE
        SELECT -1 AS l1,
               0 AS EventClass,
               0 DatabaseName,
               0 AS Filename,
               0 AS Duration,
               0 AS StartTime,
               0 AS EndTime,
               0 AS ChangeInSize;
END TRY
BEGIN CATCH
    SELECT -100 AS l1,
           ERROR_NUMBER() AS EventClass,
           ERROR_SEVERITY() DatabaseName,
           ERROR_STATE() AS Filename,
           ERROR_MESSAGE() AS Duration,
           1 AS StartTime,
           1 AS EndTime,
           1 AS ChangeInSize;
END CATCH;

USE [master];

BEGIN TRY

(

SELECT CONVERT(INT, value_in_use)

FROM sys.configurations

WHERE name = 'default trace enabled'

) = 1

BEGIN

DECLARE @curr_tracefilename VARCHAR(500);

DECLARE @base_tracefilename VARCHAR(500);

DECLARE @indx INT;

SELECT @curr_tracefilename = path

FROM sys.traces

WHERE is_default = 1;

SET @curr_tracefilename = REVERSE(@curr_tracefilename);

SELECT @indx = PATINDEX('%\%', @curr_tracefilename);

SET @curr_tracefilename = REVERSE(@curr_tracefilename);

SET @base_tracefilename = LEFT(@curr_tracefilename, LEN(@curr_tracefilename) - @indx) + '\log.trc';

SELECT

--(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,

ServerName AS [SQL_Instance],

--CONVERT(INT, EventClass) AS EventClass,

DatabaseName AS [Database_Name],

Filename AS [Logical_File_Name],

(Duration / 1000) AS [Duration_MS],

CONVERT(VARCHAR(50), StartTime, 100) AS [Start_Time],

--EndTime,

CAST((IntegerData * 8.0 / 1024) AS DECIMAL(19, 2)) AS [Change_In_Size_MB]

FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)

WHERE EventClass >= 92

AND EventClass <= 95

--AND ServerName = @@SERVERNAME

--AND DatabaseName = 'myDBName'

ORDER BY DatabaseName,

StartTime DESC;

END;

ELSE

SELECT -1 AS l1,

0 AS EventClass,

0 DatabaseName,

0 AS Filename,

0 AS Duration,

0 AS StartTime,

0 AS EndTime,

0 AS ChangeInSize;

END TRY

BEGIN CATCH

SELECT -100 AS l1,

ERROR_NUMBER() AS EventClass,

ERROR_SEVERITY() DatabaseName,

ERROR_STATE() AS Filename,

ERROR_MESSAGE() AS Duration,

1 AS StartTime,

1 AS EndTime,

1 AS ChangeInSize;

END CATCH;

me -v --db mssql &>> blog

BI subiektywnie i bez cenzury...

Tag: autogrowth

Rozrost tempdb – Extended Events + podgląd via TSQL

Autogrowth events – skrypt na detale rozrostu MDF / NDF i LDF z default trace’a