Use SQL Server Default Trace to Find Errors

By:Eli Leiba |   Last Updated: 2019-02-18   |  |   Related Tips:More > Profiler and Trace

Problem

The requirement is to build a method of reporting all errors that were collected by the default SQL Server trace. This help as a monitoring tool that captures and reports all errors, so that they can be researched and fixed by the DBA.

Solution

My solution involves creating a T-SQL stored procedure in the SQL Server master database called dbo.usp_FindErrorsInDefTrace, that will collect all error information from the SQL Server's default trace file.

Check and Enable the Default SQL Server Trace

In order to be able to get the default trace information the 'default trace enabled' option should be enabled. A value of 1 is (ON) and a value of 0 (OFF).  The 'default trace enabled' option is an advanced option.

If you are using the sp_configure system stored procedure to change this option, you can only change the default trace enabled option when the show advanced options is set to 1. The change takes effect immediately without restart.

You can run the following to show advanced options:

sp_configure 'show advanced options', '1'
RECONFIGURE

To check that the configuration option for 'default trace enabled', run sp_configure and check that config_value = 1 and the run_val = 1 as shown below.

sp_configure

If for some reason this option is not on, you can use the following to turn it on:

sp_configure 'default trace enabled', '1'
RECONFIGURE

SQL Server Stored Procedure to Find Errors in the Default Trace

The procedure first queries the sys.fn_trace_getinfo system table function in order to find the default trace file name. The sys.fn_trace_getinfo is a function that is used to return information about a specified trace or all existing traces on the server.  When it is used with DEFAULT and parameter values trace id = 1 and property = 2, it returns the default trace file name.  Then it will use function ::fn_trace_gettable that will return the contents of this trace file in a tabular form. The results are extracted from the ::fn_trace_gettable system table function and are filtered to show only rows where the error value is NOT NULL.

Here is the code.

-- =================================================================================
-- Author: Eli Leiba
-- Create date: 02-2019
-- Procedure Name: dbo.usp_FindErrorsInDefTrace
-- Description: This procedure reports all error details collected by the SQL Server default trace file
-- ==================================================================================

USE master
GO

CREATE PROCEDURE dbo.usp_FindErrorsInDefTrace
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @traceFileName NVARCHAR (500)

   SELECT @traceFileName = CONVERT (NVARCHAR (500), value)
   FROM sys.fn_trace_getinfo (DEFAULT)
   WHERE traceid = 1
      AND property = 2

   SELECT 
      t.TextData,
      t.DatabaseName,
      t.NTUserName,
      t.NTDomainName,
      t.HostName,
      t.ClientProcessID,
      t.ApplicationName,
      t.LoginName,
      t.SPID,
      t.StartTime,
      t.ServerName,
      t.Error,
      t.SessionLoginName
   FROM ::fn_trace_gettable(@traceFileName, DEFAULT) t
   WHERE t.ERROR IS NOT NULL

   SET NOCOUNT OFF
END
GO

Example Execution to find Errors in the Default SQL Server Trace

To find errors from the default trace, run the following:

USE master
GO

EXEC dbo.usp_FindErrorsInDefTrace
GO

These are the results from my server, I split the output into multiple images so it was easier to read.

Next Steps

• You can create and compile this simple procedure in your master database and use it as a simple T-SQL tool for find information about errors in the default SQL Server trace.
• The procedure was tested on SQL Server 2014 and SQL Server 2017, but should work with most versions.

Last Updated: 2019-02-18

About the author