Today as I entered the Office, I came know that we were receiving alerts stating that the transaction log files of tempdb on one of the instances was almost full. This instance was running SQL Server 2000. I quickly looked at the transaction log file of tempdb and shocked to see that the initial size was set at 250 MB and auto growth of 100 MB! Also there were a lot of 833 errors in the Event Log. Everytime templog would take a lot of time to grow because of this and as a result the transaction which needed more tempdb transaction log space would get delayed/terminated. Even though I was able to diagonize the issue, I was not able to get the number of times the templog file tried to “Auto Grow”. I wish this instance was running SQL Server 2005 or above.
What changed in SQL Server 2005 in this regard? The Default Trace. Since SQL Server 2005 and above have the ability to run the Default Trace. This trace captures almost all the events that a DBA would need to torubleshoot.
Check if the Default Trace is running:
select name, value_in_use from sys.configurations where name='default trace enabled'
If value_in_use = 1 then the Default Trace is enabled.
Enable Default Trace if not running:
sp_configure 'default trace enabled', 1 go reconfigure with override go
Once the default trace starts capturing the data, executing the below query will give the details of how many times did the database auto grow.
DECLARE @trcfilename VARCHAR(1000); SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1 SELECT StartTime, DB_NAME(databaseid)as DatabaseName, Filename, SUM ((IntegerData*8)/1024) AS [Growth in MB], (Duration/1000)as [Duration in seconds] FROM ::fn_trace_gettable(@trcfilename, default) WHERE (EventClass = 92 OR EventClass = 93) GROUP BY StartTime,Databaseid, Filename, IntegerData, Duration order by StartTime
This output can be further analyzed to check if the initial size and auto growth settings of a database has been properly set.