SQL Server - Datafile auto-growth events

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:

Results screen

Script messages after execution:

C:\Program Files\Microsoft SQL Server\MSSQL14.MGMT\MSSQL\Log\log.trc

Script results detailed description:

Column nameData typeDescription
DatabaseNamesysnameDatabase name that was affected by event.
DatabaseFileNamenvarchar(260)Operating-system file name.
FileTypenvarcharType of datafile. Can be one of values: ROW, LOG, FILESTREAM or FULL-TEXT.
EventNamenvarchar(128)Unique name of this event. This parameter is not localized.
EventGrowthMBdecimal(9,2)Number of 8-kilobyte (KB) pages divided by 1024 by which the file increased.
EventTimedatetimeStart time of event.
EventDurationSecdecimal(19,2)Length of time (in seconds) necessary to extend the file.
CurrentAutoGrowthSetvarchar(30)Actual autogrowth setting of affected database. Can be given in MB or %.
CurrentFileSizeMBdecimal(19,2)Actual size of data file in MB.

Leave a Reply

Your email address will not be published. Required fields are marked *