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

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

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

Dodaj komentarz Anuluj pisanie odpowiedzi