Get SQL Server Trace automatically started at service startup

Although SQL Trace and SQL Server Profiler have been marked as deprecated and Microsoft recommends to use Extended Events instead, many of us still use SQL Profiler. You may run the Profiler for small traces or long traces but but if you need to make sure that it runs continuously even at the server restart, you need to consider following points;

• SQL Server Profiler is not recommended for long-term monitoring and large traces.
• Profiler cannot be automatically started when the server is restarted.

If you still need to implement it, you can consider;

• Use SQL Trace instead.
• Use sp_procoption to start the trace at the SQL Server Service start.

This makes sure that the trace configured is continuously running even when the server is restarted and the output is saved and queried anytime.

In order to implement this, you need to follow the steps below;

1. Start with SQL Server Profile and select all events you need with columns and filters for trace.
2. Get the definition of the created using the Profiler and use it for creating the SQL Trace.
3. Include the code of SQL Trace with a stored procedure and add the stored procedure as a startup procedure.

Here is an sample implementation with details.

First let's make a Trace using the Profiler. The following Trace is created with TSQL_Duration Template and All Columns is selected for both Events.

Let's click on Run and then immediately stop. In order to get the code generated for SQL Trace, click File -> Export -> Script Trace Definition -> For SQL Server 2005 - 2016... menu item.

Save it as Trace01.sql. Then open the saved script in Query Window for modification. This code has a place to modify which sets the trace output file path. Since we need to make sure that this code can be executed at each server restart, a unique output file name is required. Therefore, let's add a variable called @FilePath and set with sp_trace_create statement. This is how the first part of the code is changed now;

/****************************************************/
/* Created by: SQL Server 2016 Profiler          */
/* Date: 12/21/2016  10:28:14 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @FilePath nvarchar(200);
SET @FilePath = N'E:\TraceOutput\SQLDurationTrace_' 
 + CONVERT(nvarchar(20), GetDate(), 112) + N'_' + REPLACE(CONVERT(nvarchar(20), GetDate(), 108), ':', N'')

exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
if (@rc != 0) goto error

Note that, this creates a file named as SQLDurationTrace_YYYYMMDD_HHMMSS.

It is better to record the TraceID generated from this because it is required to stop and close the trace if required. Since this is going start automatically, the generated TraceID cannot be taken out as it is written. Therefore we need to change it as well. Let's create a table for holding generated trace ids. Let's create this table in master database.

CREATE TABLE dbo.TraceID
(
 ServerStartDateTime datetime NOT NULL 
  PRIMARY KEY DEFAULT(GetDate())
 , TraceID int  NULL
);
GO

Scroll down the Trace code generated and comment the select TraceID=@TraceID statement and add the following instead.

-- display trace id for future references
--select TraceID=@TraceID
INSERT INTO dbo.TraceID
 (ServerStartDateTime, TraceID)
VALUES
 (DEFAULT, @TraceID);

goto finish

We have done the necessary changes to the Trace code. Let's place the entire code into a stored procedure and name it as StartTrace. Here is the entire code now;

USE master;
GO

CREATE OR ALTER PROC dbo.StartTrace
AS
BEGIN

 -- Create a Queue
 declare @rc int
 declare @TraceID int
 declare @maxfilesize bigint
 set @maxfilesize = 5 

 DECLARE @FilePath nvarchar(200);
 SET @FilePath = N'E:\TraceOutput\SQLDurationTrace_' 
  + CONVERT(nvarchar(20), GetDate(), 112) + N'_' + REPLACE(CONVERT(nvarchar(20), GetDate(), 108), ':', N'')

 exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
 if (@rc != 0) goto error

 -- Client side File and Table cannot be scripted

 -- Set the events
 declare @on bit
 set @on = 1
 exec sp_trace_setevent @TraceID, 10, 1, @on
 exec sp_trace_setevent @TraceID, 10, 9, @on
 exec sp_trace_setevent @TraceID, 10, 2, @on
 exec sp_trace_setevent @TraceID, 10, 66, @on
 exec sp_trace_setevent @TraceID, 10, 10, @on
 exec sp_trace_setevent @TraceID, 10, 3, @on
 exec sp_trace_setevent @TraceID, 10, 4, @on
 exec sp_trace_setevent @TraceID, 10, 6, @on
 exec sp_trace_setevent @TraceID, 10, 7, @on
 exec sp_trace_setevent @TraceID, 10, 8, @on
 exec sp_trace_setevent @TraceID, 10, 11, @on
 exec sp_trace_setevent @TraceID, 10, 12, @on
 exec sp_trace_setevent @TraceID, 10, 13, @on
 exec sp_trace_setevent @TraceID, 10, 14, @on
 exec sp_trace_setevent @TraceID, 10, 15, @on
 exec sp_trace_setevent @TraceID, 10, 16, @on
 exec sp_trace_setevent @TraceID, 10, 17, @on
 exec sp_trace_setevent @TraceID, 10, 18, @on
 exec sp_trace_setevent @TraceID, 10, 25, @on
 exec sp_trace_setevent @TraceID, 10, 26, @on
 exec sp_trace_setevent @TraceID, 10, 31, @on
 exec sp_trace_setevent @TraceID, 10, 34, @on
 exec sp_trace_setevent @TraceID, 10, 35, @on
 exec sp_trace_setevent @TraceID, 10, 41, @on
 exec sp_trace_setevent @TraceID, 10, 48, @on
 exec sp_trace_setevent @TraceID, 10, 49, @on
 exec sp_trace_setevent @TraceID, 10, 50, @on
 exec sp_trace_setevent @TraceID, 10, 51, @on
 exec sp_trace_setevent @TraceID, 10, 60, @on
 exec sp_trace_setevent @TraceID, 10, 64, @on
 exec sp_trace_setevent @TraceID, 12, 1, @on
 exec sp_trace_setevent @TraceID, 12, 9, @on
 exec sp_trace_setevent @TraceID, 12, 3, @on
 exec sp_trace_setevent @TraceID, 12, 11, @on
 exec sp_trace_setevent @TraceID, 12, 4, @on
 exec sp_trace_setevent @TraceID, 12, 6, @on
 exec sp_trace_setevent @TraceID, 12, 7, @on
 exec sp_trace_setevent @TraceID, 12, 8, @on
 exec sp_trace_setevent @TraceID, 12, 10, @on
 exec sp_trace_setevent @TraceID, 12, 12, @on
 exec sp_trace_setevent @TraceID, 12, 13, @on
 exec sp_trace_setevent @TraceID, 12, 14, @on
 exec sp_trace_setevent @TraceID, 12, 15, @on
 exec sp_trace_setevent @TraceID, 12, 16, @on
 exec sp_trace_setevent @TraceID, 12, 17, @on
 exec sp_trace_setevent @TraceID, 12, 18, @on
 exec sp_trace_setevent @TraceID, 12, 26, @on
 exec sp_trace_setevent @TraceID, 12, 31, @on
 exec sp_trace_setevent @TraceID, 12, 35, @on
 exec sp_trace_setevent @TraceID, 12, 41, @on
 exec sp_trace_setevent @TraceID, 12, 48, @on
 exec sp_trace_setevent @TraceID, 12, 49, @on
 exec sp_trace_setevent @TraceID, 12, 50, @on
 exec sp_trace_setevent @TraceID, 12, 51, @on
 exec sp_trace_setevent @TraceID, 12, 60, @on
 exec sp_trace_setevent @TraceID, 12, 64, @on
 exec sp_trace_setevent @TraceID, 12, 66, @on


 -- Set the Filters
 declare @intfilter int
 declare @bigintfilter bigint

 exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks2014'
 -- Set the trace status to start
 exec sp_trace_setstatus @TraceID, 1

 -- display trace id for future references
 --select TraceID=@TraceID
 INSERT INTO dbo.TraceID
  (ServerStartDateTime, TraceID)
 VALUES
  (DEFAULT, @TraceID);

 goto finish

 error: 
 select ErrorCode=@rc

 finish: 
END
GO

Now the last step. We need to add this stored procedure as a startup procedure. Use the following code to add StartTrace as a Startup procedure;

USE master;
GO

EXEC sp_procoption StartTrace, 'STARTUP', 'ON';
GO

Note that, in order get this work, we need to make sure scan for startup procs server setting is enabled. Generally, when the sp_procoption is execute, this is getting enabled but it is always better to get it enabled before using sp_configure. Done. Let's restart the SQL Server Service and check the dbo.TraceID table.

As you see, a record is inserted with an ID. This means that trace has been started. Let's check the folder.

File is created. For testing purposes, let's run some queries in AdventureWorks2014 database and restarted the service.

USE AdventureWorks2014;
GO

SELECT * FROM Production.Product;
SELECT * FROM Sales.Customer;

Once restarted, we should see a new record in the table and new file in the folder.

This means that our Trace is getting automatically started when the service is started and trace continues. You can query the trace, current or older ones using fn_trace_gettable function and if you need, you can stop the current Trace using value 1 and lose the current Trace using value 2 with sp_trace_setstatus stored procedure.