how can I add the date and time on the autogrowth query below?
0
votes
1
answer
59
views
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?
Asked by Marcello Miorelli
(17274 rep)
Jul 10, 2024, 11:37 AM
Last activity: Jul 10, 2024, 03:02 PM
Last activity: Jul 10, 2024, 03:02 PM