SQL Server Utilities - Use SQL Trace to capture database errors

This is a link to previous related posts that you may find interesting

https://sqlserverutilities.com/maximize-sql-trace/

https://sqlserverutilities.com/use-sql-trace-to-capture-database-exceptions/

If we want to capture all the commands being sent to the database engine we should capture just these 2 events: SQL:BatchCompleted and
RPC:Completed. By doing so we guarantee to be capturing all executions sent to the database engine which can come in 2 forms, RPC – that is stored procedures or Batch – that is any code generated at the client and sent to the database engine.

Each of these 2 events have the Error column which we can be populated with 3 different values. 0 representing a success execution, that is an execution that has completed with a return status of 0 indicating it completed successfully. The other 2 values are 1 or 2 representing a failed execution, that is an execution that terminated with a return status of 1 or 2 indicating it failed to complete successfully.

By setting up a trace defined to capture these 2 events (SQL:BatchCompleted and RPC:Completed) while filtering on the Error column for values different than 0 we actually capture all executions that completed with a return status of 1 and 2 indicating failed executions.

This is a very simple yet powerful tool allowing you to capture very little data which means that at a cost of a minimal footprint (or immeasurable I would say) you get valuable information effortless.

For example such a trace will capture all executions that Timed Out including the actual command with parameters and much more.

To set up such a trace is pretty simple, just follow these 2 steps

create the TraceErrors stored procedure
execute the procedure to start the trace

The command to execute the procedure

EXEC DBA.dbo.TraceErrors @MaxFileSize = 50, @FileCount = 10, @Path = ‘D:\Traces\TraceErrors’;

Here is a link to my Traces project on GitHub where you will find the TraceErrors stored procedure

Here is a link to a previous post showing how to get your traces automatically started during a service restart using a startup stored procedure in order to have them constantly running.