How many times did my database auto grow?

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

Read more: SQL Server pedia

Posted via email from .NET Info