Default Trace Queries

default-trace-auto-grow-shrink.sql

WITH DefaultTrace AS
(
	SELECT
		DF1.DatabaseName AS database_name
	,	DF1.[Filename] AS database_file_name
	,	TE1.[name] AS event_type
	,	DF1.StartTime AS event_start_time
	,	DF1.EndTime AS event_end_time
	,	CAST(DF1.Duration / 1000. AS bigint) AS duration_ms
	,	CAST(DF1.Duration / 1000000. AS decimal(10, 3)) AS duration_sec
	,	CAST(DF1.IntegerData as bigint) * 8192  AS change_bytes
	,	CAST(DF1.IntegerData as bigint) * 8192 / 1024. / 1024.  AS change_MB
	FROM	fn_trace_gettable(
				(SELECT  REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 256)) + 'log.trc' AS trace_filename
				FROM	sys.traces
				WHERE	is_default = 1)
			,	default
			) AS DF1
	JOIN	sys.trace_events AS TE1
	  ON	DF1.EventClass = TE1.trace_event_id
	WHERE	
		TE1.trace_event_id BETWEEN 92 AND 95
		--TE1.trace_event_id IN (92, 93, 94, 95)
		--TE1.[name] IN (N'Data File Auto Grow', N'Log File Auto Grow', N'Data File Auto Shrink', N'Log File Auto Shrink')
)
SELECT
	T1.database_name, T1.event_type, T1.database_file_name
,	MIN(event_start_time) AS min_event_start_time, MAX(event_end_time) AS max_event_end_time, COUNT(*) AS total_events
,	MIN(duration_sec) AS min_duration_sec, MAX(duration_sec) AS max_duration_sec, AVG(duration_sec) AS avg_duration_sec, SUM(duration_sec) AS total_duration_sec
,	MIN(change_MB) AS min_change_MB, MAX(change_MB) AS max_change_MB, AVG(change_MB) AS avg_change_MB, SUM(change_MB) AS total_change_MB
FROM	DefaultTrace AS T1
GROUP BY
	T1.database_name, T1.event_type, T1.database_file_name
ORDER BY
	T1.database_name, T1.event_type, T1.database_file_name