Check for auto-growth events

[alinselicean]

Hi

Is there a way to check for auto-growth in DBADash? I tried figuring this out and couldn't find anything.

Thanks.
Alin

[DavidWiseman]

Not directly. DBA Dash does collect file sizes though so you can write a query that reports on the change in file size over a given period. You can calculate the number of autogrowths - this assumes that files were not grown manually.

SELECT I.InstanceDisplayName,
		D.name AS DB, 
		F.name AS [File],
		F.size/128.0 AS SizeMB,
		(F.size-SS.Size)/128.0 AS TotalGrowthMB, /* Diff between file size now and 2 days ago */
		F.is_percent_growth,
		CASE WHEN F.is_percent_growth=1 THEN F.growth ELSE NULL END AS AutoGrowthPct,
		CASE WHEN F.is_percent_growth=1 THEN F.growth * 0.01 * SS.Size ELSE F.growth END /128.0 AS AutoGrowthMB, /* Growth in MB  - converting % growth into MB */
		CAST((F.size-SS.Size) / NULLIF(CASE WHEN F.is_percent_growth=1 THEN F.growth * 0.01 * SS.Size ELSE F.growth END,0) AS INT) AS AutoGrowCount /* Calculate autogrowth count based on change in size and autogrpowth increment.  Note: Files could have been grown manually */
FROM dbo.Instances I 
JOIN dbo.Databases D ON D.InstanceID = I.InstanceID
JOIN dbo.DBFiles F ON F.DatabaseID = D.DatabaseID
OUTER APPLY (SELECT TOP(1) FSS.Size
			FROM dbo.DBFileSnapshot FSS 
			WHERE FSS.FileID = F.FileID
			AND FSS.SnapshotDate >= CAST(DATEADD(d,-2,GETUTCDATE()) AS DATETIME2(2))
			ORDER BY FSS.SnapshotDate
			) SS /* Get the file size from 2 days ago */
WHERE I.IsActive=1
AND F.IsActive=1
AND D.IsActive=1
AND F.size-SS.Size>0
ORDER BY AutoGrowCount DESC

DBA Dash does have the option to monitor space within DB files if you are interested in growing files manually to avoid autogrowth. In the Files tab you can configure thresholds to warn on free space within DB files.

[DavidWiseman]

This can now be implemented as a custom report:

CREATE OR ALTER PROC UserReport.FileGrowth(
	@InstanceIDs IDs READONLY,
	@Days INT = 2
)
AS
SELECT I.InstanceDisplayName AS [Instance],
		D.name AS DB, 
		F.name AS [File],
		F.size/128.0 AS [Size MB],
		(F.size-SS.Size)/128.0 AS [Total Growth MB], /* Diff between file size now and 2 days ago */
		F.is_percent_growth AS [Is Pct Growth],
		CASE WHEN F.is_percent_growth=1 THEN F.growth/100.0 ELSE NULL END AS [Auto Growth Pct],
		CASE WHEN F.is_percent_growth=1 THEN F.growth * 0.01 * SS.Size ELSE F.growth END /128.0 AS [Auto Growth MB], /* Growth in MB  - converting % growth into MB */
		CAST((F.size-SS.Size) / NULLIF(CASE WHEN F.is_percent_growth=1 THEN F.growth * 0.01 * SS.Size ELSE F.growth END,0) AS INT) AS [Estimated Auto Grow Count] /* Calculate autogrowth count based on change in size and autogrpowth increment.  Note: Files could have been grown manually */
FROM dbo.Instances I 
JOIN dbo.Databases D ON D.InstanceID = I.InstanceID
JOIN dbo.DBFiles F ON F.DatabaseID = D.DatabaseID
OUTER APPLY (SELECT TOP(1) FSS.Size
			FROM dbo.DBFileSnapshot FSS 
			WHERE FSS.FileID = F.FileID
			AND FSS.SnapshotDate >= CAST(DATEADD(d,-@Days,GETUTCDATE()) AS DATETIME2(2))
			ORDER BY FSS.SnapshotDate
			) SS /* Get the file size from 2 days ago */
WHERE I.IsActive=1
AND F.IsActive=1
AND D.IsActive=1
AND F.size-SS.Size>0
AND EXISTS(SELECT 1 
			FROM @InstanceIDs T 
			WHERE T.ID = I.InstanceID
			)
ORDER BY [Estimated Auto Grow Count] DESC
OPTION(RECOMPILE);
GO

DELETE dbo.CustomReport 
WHERE ProcedureName = 'FileGrowth'
AND SchemaName = 'UserReport'

INSERT INTO dbo.CustomReport
(
    SchemaName,
    ProcedureName,
    MetaData
)
VALUES
( N'UserReport', N'FileGrowth', N'{
  "ReportName": "File Growth",
  "Description": "This report shows database files that have grown in the last 2 days (either manual or auto grow).  Note: The Auto Grow Count is just the calculated based off the auto grow settings and the change in file size. ",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {
        "Size MB": "N0",
        "Total Growth MB": "N0",
        "Auto Growth MB": "N0",
        "Auto Growth Pct": "P0"
      },
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result0"
    }
  }
}' )