Blog | Nov 29, 2012

Default Trace – Unearthing A Hidden Gem

 

Default Trace – unearthing a hidden gem

Authors: Satya Prakash and Ravjeet Singh – TriCore Solutions [India]

Last week as we were working on an issue being reported at one of our customer’s environment where the Data file of a Production database was growing at a very rapid pace. A daily process was failing because of this issue. As per the standards we were not allowed to set Auto-growth. We then decided that before this issue becomes more visible we proactively performed an RCA. This is where the default trace option of SQL Server came to our rescue and we were able to report the exact cause.

So, what is this default trace all about? default trace

Default Trace is one of MS SQL Server’s hidden gems. We, at TriCore Solutions, use this extensively as it can provide us with lot of valuable information about the database.

It is a lightweight trace which is enabled by default and captures useful information in the background without putting any considerable load on server. Following are the categories of events it captures:

Categories

Events

Database

  • Data/Log file auto grow
  • Data/Log file auto shrink
  • Database mirroring status change

Errors and Warnings

 

  • Error log
  • Hash warning
  • Missing Column Statistics
  • Missing Join Predicate
  • Sort Warning

Object events

  • Object Created/Altered/Deleted

Full-Text

  • FT Crawl Started/Aborted/Stopped

Security Audit

  • Add DB user event
  • Add login to server role event
  • Add Member to DB role event
  • Add Role event
  • Add login event
  • Backup/Restore event
  • Change Database owner
  • DBCC event
  • Database Scope GDR event (Grant, Deny, Revoke)
  • Login Change Property event
  • Login Failed
  • Login GDR event
  • Schema Object GDR event
  • Schema Object Take Ownership
  • Server Starts and Stops


With the above information, a DBA can identify:

1)       Who filled up data or transaction log file of the database? Related alerts could be “Error 1105 - Cannot Allocate Space” or “Could not allocate space for object 'object_name' in database 'database_name' because the 'PRIMARY' filegroup is full” error. Using the default trace you can identify the SPID details which filled the data/log file

2)      Missing indexes or missing stats by following “errors and Warnings” event category

3)      Someone deleted or altered object in a database and you need to identify

4)      Errors in Full text catalog and helps prepare security audit report.

How to know if default trace is enabled or not? The below query would help you identify if Default Trace is enabled.

SELECT * FROM sys.configurations WHERE configuration_id = 1568

How to read default trace?

The default trace logs can be opened and examined by SQL Server Profiler or queried with Transact-SQL by using the fn_trace_gettable system function. SQL Server Profiler can open the default trace log files just as it does normal trace output files. Following is the example for opening trace file using fn_trace_gettable function.

SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log.trc', default)

GO


Location and History in Default Trace?

The default trace log is by default stored in the \MSSQL\LOG directory using a rollover trace file. The base file name for the default trace log file is log.trc. In a typical SQL Server installation where this is enabled, the Trace ID is 1. By default there are five trace files of 20MB each and there is no known supported method of changing this. In a busy system, the trace files may roll over far too fast and you may not catch some of the changes.

We have been using Default trace for a while now, but this one incident really put our team in the driver’s seat. We unearthed how useful this option is in such cases. We were able to provide the exact details causing the issue and report it to application team…they were able to fix it then!

The below sample script can get you the data in the default trace files.

declare @folder_path varchar(1000),@trc_filename varchar(1000),@min int,@max int

create table #get_file_list (filename varchar(1000),depth tinyint,is_file bit)   

create table #get_trc_file_list (sno int identity, filename varchar(1000),depth tinyint,is_file bit)  

create table #get_trace_file_details (filename varchar(1000),File_have_data_from datetime)     

SELECT  @trc_filename =convert(varchar(500),value) 
FROM ::fn_trace_getinfo(default) where traceid=1 and property=2

select @folder_path=left(@trc_filename,charindex('\LOG_',UPPER(@trc_filename))-1)

insert into #get_file_list
exec master.dbo.xp_dirtree @folder_path,1,1

insert into #get_trc_file_list
select * from  #get_file_list where UPPER(filename) like '%LOG_%.TRC'

select @min=MIN(sno),@max=max(sno) from #get_trc_file_list

while (@min<=@max)

Begin

select @trc_filename=@folder_path+'\'+filename from #get_trc_file_list where sno=@min

insert into #get_trace_file_details
SELECT
       @trc_filename
       ,min(StartTime) 
 FROM ::fn_trace_gettable(@trc_filename,100) T
 set @min=@min+1
End

select * from #get_trace_file_details order by File_have_data_from desc
GO