Looks for auto-growth events in default SQL Server trace. You can find this information in SSMS built-in database standard report Disk usage under charts. This part of report is available only if there were some events for given database.
This script is some kind of auto-growth summary for all databases in instance. Can be useful when you are investigating some occasional disk drive fulfillment. It reads event data from default SQL Server trace, stored on path returned in result messages.
TIP: Use this script to check if there are some databases with not properly configured auto-growth which can lead to data file fragmentation.
DECLARE @TracePath NVARCHAR(500)
SELECT @TracePath = path FROM sys.traces WHERE is_default = 1
SELECT @TracePath = SUBSTRING(@TracePath, 0, CHARINDEX('\log_', @tracepath, 0) + 4) + '.trc'
PRINT @TracePath
SELECT
g.DatabaseName AS DatabaseName,
mf.physical_name AS DatabaseFileName,
CASE mf.type
WHEN 0 THEN 'Row'
WHEN 1 THEN 'Log'
WHEN 2 THEN 'FILESTREAM'
WHEN 4 THEN 'Full-text'
END AS FileType,
te.name AS EventName,
CONVERT(DECIMAL(19, 2), g.IntegerData * 8 / 1024.) AS EventGrowthMB, -- Number of 8-kilobyte (KB) pages by which the file increased.
g.StartTime AS EventTime,
CONVERT(DECIMAL(19, 2), g.Duration / 1000. / 1000.) AS EventDurationSec, -- Length of time (in milliseconds) necessary to extend the file.
CASE
WHEN mf.is_percent_growth = 1 THEN CONVERT(CHAR(2), mf.growth) + '%'
ELSE CONVERT(VARCHAR(30), CONVERT(DECIMAL(19, 2), mf.growth * 8. / 1024.)) + 'MB'
END AS CurrentAutoGrowthSet,
CONVERT(DECIMAL(19, 2), mf.size * 8. / 1024.) AS CurrentFileSizeMB
FROM fn_trace_gettable(@tracepath, DEFAULT) g
CROSS APPLY sys.trace_events te
INNER JOIN sys.master_files mf ON mf.database_id = g.DatabaseID AND g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id
AND te.name IN (
'Data File Auto Grow'
,'Log File Auto Grow'
)
ORDER BY StartTime DESC
Results
Script results screen:
Script messages after execution:
C:\Program Files\Microsoft SQL Server\MSSQL14.MGMT\MSSQL\Log\log.trc
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| DatabaseName | sysname | Database name that was affected by event. |
| DatabaseFileName | nvarchar(260) | Operating-system file name. |
| FileType | nvarchar | Type of datafile. Can be one of values: ROW, LOG, FILESTREAM or FULL-TEXT. |
| EventName | nvarchar(128) | Unique name of this event. This parameter is not localized. |
| EventGrowthMB | decimal(9,2) | Number of 8-kilobyte (KB) pages divided by 1024 by which the file increased. |
| EventTime | datetime | Start time of event. |
| EventDurationSec | decimal(19,2) | Length of time (in seconds) necessary to extend the file. |
| CurrentAutoGrowthSet | varchar(30) | Actual autogrowth setting of affected database. Can be given in MB or %. |
| CurrentFileSizeMB | decimal(19,2) | Actual size of data file in MB. |