how can I add the date and time on the autogrowth query below?

Question

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?

score 0 · Accepted Answer · answered Jul 10 '24 at 15:02

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

how can I add the date and time on the autogrowth query below?

1 Answers1