“The SQL Guy” Post # 11: SQL Server’s Black Box Can Be Very Helpful

  • Article

SQLServerEver wanted to find out what was happening when SQL Server crashed? Your investigation should also include reviewing the SQL Server activities much like what profiler would do. SQL Server 2005 (including 2008, 2008R2 and the upcoming 2012) come with a default trace enabled out of the box. This trace keeps track of configuration changes, process level information and other information that can be very helpful for troubleshooting SQL Server related issues.

 

The default trace file can be opened and examined by launching SQL Server Profiler and by loading the log.trc file from ( \Program Files\Microsoft SQL Server\<Instance_Name>\MSSQL\Log\) location or by querying it with Transact-SQL using the fn_trace_gettable system function.

 

Alternatively, you can query the trace file using the following T-SQL statement:

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

-- Make sure you point to the right drive instead of C:\ drive.

 

Note: By default this trace is onand can’t be stopped by using the SP_Trace_SetStatus system stored procedure. Instead, you will need to stop the default trace by using SP_Configure option, but it is not recommended (or a really good idea) to disable this tracing since the overhead is low and the data it provides can be a very handy troubleshooting tool.

Some of the information provided by the default trace includes:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Queries by Average CPU time
Top Queries by Average IO,

Etc.

This data can also be accessed from the Admin reports (from SQL Server Management Studio right-click the registered server and select "Reports")

DamirB-BlogSignature