File Growth Analysis via Default Trace
SQL Server continuously tracks certain critical events in the “default trace”. Among those events are the file growth events triggered when SQL Server automatically grows a database data or log file, or when a user manually expands a file.
The code below shows a simple method of seeing file growth events contained in the default trace.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
DECLARE @trcfilename nvarchar(260); DECLARE @MinEventSequence int; DECLARE @MaxEventSequence int; SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1; IF OBJECT_ID('tempdb..#trctemp') IS NOT NULL BEGIN DROP TABLE #trctemp; END SELECT * INTO #trctemp FROM sys.fn_trace_gettable(@trcfilename, default) tt /* Specify a time-range for our query. Below example will only show events that are at most 60 minutes old. */ SELECT @MinEventSequence = MAX(t.EventSequence) FROM #trctemp t WHERE t.StartTime < = DATEADD(MINUTE, -60, GETDATE()); SELECT @MaxEventSequence = MIN(t.EventSequence) FROM #trctemp t WHERE t.StartTime >= DATEADD(MINUTE, 0, GETDATE()); IF @MaxEventSequence IS NULL SELECT @MaxEventSequence = MAX(t.EventSequence) FROM #trctemp t; SELECT tt.EventSequence , [Database] = d.name , [Event] = te.name , tt.HostName , tt.ApplicationName , tt.LoginName , DurationInSeconds = CONVERT(decimal(10,3), tt.Duration / 1000000e0) , tt.StartTime , tt.EndTime , tt.ServerName , tt.FileName FROM #trctemp tt INNER JOIN master.sys.databases d ON tt.databaseid = d.database_id INNER JOIN sys.trace_events te ON tt.EventClass = te.trace_event_id WHERE tt.EventSequence >= @MinEventSequence AND tt.EventSequence < = @MaxEventSequence AND EventClass IN ( 92 --Data File Auto Grow , 93 --Log File Auto Grow , 94 --Data File Auto Shrink , 95 --Log File Auto Shrink ) ORDER BY tt.EventSequence; </code> |
To test this code, I created a simple, small database, and populated it with some data. Here’s the code to do that, which will drop any database you happen to have named ‘TestDB’:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
USE master; GO IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestDB') DROP DATABASE TestDB; GO CREATE DATABASE TestDB ON (NAME = TestDB_system, FILENAME = 'C:\TEMP\TestDB_system.mdf' , SIZE = 4MB, MAXSIZE = 100MB, FILEGROWTH = 10MB) LOG ON (NAME = TestDB_log, FILENAME = 'C:\TEMP\TestDB_log.ldf' , SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10MB); GO USE TestDB; GO CREATE TABLE dbo.TestTable ( TestTableID int NOT NULL CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED IDENTITY(1,1) , SomeVal NVARCHAR(1000) NOT NULL ); /* This will insert 10,000 rows into the TestTable, enough for several growth events. */ ;WITH c AS ( SELECT v1.Val FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (8)) v1(Val) ) INSERT INTO dbo.TestTable (SomeVal) SELECT CONVERT(nvarchar(1000), CRYPT_GEN_RANDOM(1000)) FROM c C1 CROSS JOIN c C2 CROSS JOIN c C3 CROSS JOIN c C4; |
After creating the test database, and growing it by inserting the test data, I ran the analysis code from the top of this post, which shows the following output:
As you can see in the image above, the file growth events for this database, and it’s log files, are clearly outlined. There are four file growth events, each taking less than a second to complete; two for the data files, and two for the log files.
Let me know if you have any questions about this code in the comments below!