Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Use SQL Server Default Trace to Find Errors


By:   |   Updated: 2019-02-18   |   Comments (1)   |   Related: 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
sp_configure settings

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.

query output
query output
query output
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


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 20, 2019 - 2:22:48 PM - Pavan Back To Top

Can we use this SP in production environments?


Learn more about SQL Server tools