0

while trying to prevent autogrowth from happening, I got this nice script here.

however, if it does happen, I need to know how to find it out. that I can do using this very nice script by Max Vernon.

                        print @@servername + ' - ' + SUBSTRING(@@version,1,COALESCE(CHARINDEX('Copyright',@@version,0)-1,108))
                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT OFF


/*
    Description:    display growth events for all databases on the instance
    by:             Max Vernon
    date:           2014-10-01
*/
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(2000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd = 
'
DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path 
FROM sys.traces WITH(NOLOCK)
WHERE is_default = 1;

IF COALESCE(@trcfilename,'''') <> ''''
BEGIN
SELECT [Radhe]=
    '''''''' + @@SERVERNAME + '''''','''''' +
     DB_NAME(mf.database_id) + '''''','''''' +
     mf.name + '''''','' +
     CONVERT(VARCHAR(255), a.NumberOfGrowths) + '','' +
     CONVERT(VARCHAR(255), CAST(a.DurationOfGrowthsInSeconds AS decimal(38, 20)))
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);
END
ELSE
BEGIN
    SELECT @@SERVERNAME, ''NO TRACE FILE'';
END
';
EXEC sp_executesql @cmd;
END
ELSE
BEGIN
    SELECT [SERVER NAME]=@@SERVERNAME, [Product Version]=SERVERPROPERTY('ProductVersion');
END

what is missing in and I would like to add is - when did the autogrowth happen?

how can I find that out?

2
  • 2
    You can add starttime column from sys.fn_trace_gettable. See the example here by Rajendra Gupta. Commented Jul 10 at 12:30
  • 1
    amazing, that is exactly what I am looking for. thanks a lot. Commented Jul 10 at 13:41

1 Answer 1

0

following the kind comment from SqlWorldWide,

I implemented what he said:

You can add starttime column from sys.fn_trace_gettable. See the example here by Rajendra Gupta. – SqlWorldWide

and now I get the date when the autogrowth happened - including the week day.


/*
    Description:    display growth events for all databases on the instance
    by:             Max Vernon
    date:           2014-10-01
*/
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(2000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd = 
'
DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path 
FROM sys.traces WITH(NOLOCK)
WHERE is_default = 1;

IF COALESCE(@trcfilename,'''') <> ''''
BEGIN
SELECT [The autogrowths]= '''''''' + a.starttime + '''''','''''' +
    '''''''' + @@SERVERNAME + '''''','''''' +
     DB_NAME(mf.database_id) + '''''','''''' +
     mf.name + '''''','' +
     CONVERT(VARCHAR(255), a.NumberOfGrowths) + '','' +
     CONVERT(VARCHAR(255), CAST(a.DurationOfGrowthsInSeconds AS decimal(38, 20)))
    FROM
    (
        SELECT
            starttime=DATENAME(dw,MAX(tt.starttime)) + SPACE(1) + convert(varchar(30), MAX(tt.starttime), 113),
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);
END
ELSE
BEGIN
    SELECT @@SERVERNAME, ''NO TRACE FILE'';
END
print @trcfilename

';
EXEC sp_executesql @cmd;
END
ELSE
BEGIN
    SELECT [SERVER NAME]=@@SERVERNAME, [Product Version]=SERVERPROPERTY('ProductVersion');
END

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.