Use SQL Server Default Trace to Find Errors
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.
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.
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.
- 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
View all my tips