Manual steps for MSSQL SQL Trace file Create, Start, Stop and Delete

For every MSSQL Server agent two traces will be created for Applicare.

• SQLTraceProfiling.trc - To gather SQL data - 512 MB (max).
• SQLDeadlockTraceProfiling.trc - To gather Deadlock data - 10 MB (max).
• Once it reaches its maximum size it will reset. It will not go beyond their maximum limit.

Step 1: Login into MSSQL Server with 'sa' or Applicare user. The user should have sysadmin permission and create and modify permission for SQL traces in "master" database.

Step 2: Select "master" database and select "New Query".

Create SQLTraceProfiling.trc

Replace @filepath with the 'ApplicareSingleAgent home path' in the below query and execute.

declare @rc int 
declare @TraceID int 
declare @maxfilesize bigint 
set @maxfilesize = 512 
declare @Applicare_Internal varchar 
exec @rc = sp_trace_create @TraceID output, 0, N'@filepath\SQLTraceProfiling', @maxfilesize, NULL 
if (@rc != 0) goto error 
declare @on bit 
set @on = 1 
exec sp_trace_setevent @TraceID, 12, 1, @on 
exec sp_trace_setevent @TraceID, 12, 9, @on 
exec sp_trace_setevent @TraceID, 12, 11, @on 
exec sp_trace_setevent @TraceID, 12, 6, @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, 35, @on 
declare @intfilter int 
declare @bigintfilter bigint 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c6753f4b-99b0-4299-927d-e2e1031a7406'
exec sp_trace_setstatus @TraceID, 1 
select TraceID=@TraceID 
goto finish 
error: 
select ErrorCode=@rc 
finish: 
;

declare @rc int 
declare @TraceID int 
declare @maxfilesize bigint 
set @maxfilesize = 512 
declare @Applicare_Internal varchar 
exec @rc = sp_trace_create @TraceID output, 0, N'D:\Applicare\ApplicareSingleAgentWindows\SQLTraceProfiling', @maxfilesize, NULL 
if (@rc != 0) goto error 
declare @on bit 
set @on = 1 
exec sp_trace_setevent @TraceID, 12, 1, @on 
exec sp_trace_setevent @TraceID, 12, 9, @on 
exec sp_trace_setevent @TraceID, 12, 11, @on 
exec sp_trace_setevent @TraceID, 12, 6, @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, 35, @on 
declare @intfilter int 
declare @bigintfilter bigint 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c6753f4b-99b0-4299-927d-e2e1031a7406'
exec sp_trace_setstatus @TraceID, 1 
select TraceID=@TraceID 
goto finish 
error: 
select ErrorCode=@rc 
finish: 
;

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 10
declare @Applicare_Internal varchar
exec @rc = sp_trace_create @TraceID output, 0, N'@filepath\SQLDeadllockTraceProfiling', @maxfilesize, NULL
if (@rc != 0) goto error
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 27, @on
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
;

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 10
declare @Applicare_Internal varchar
exec @rc = sp_trace_create @TraceID output, 0, N'D:\Applicare_Applications\Applicare_Mysql\ApplicareSingleAgentWindows\SQLDeadllockTraceProfiling', @maxfilesize, NULL
if (@rc != 0) goto error
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 27, @on
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
;

View Trace

Select * from sys.traces;

If it is a Applicare trace then in the path column it will have the Applicare Agent Home path and at last it will end with SQLTraceProfiling.trc and SQLDeadlockTraceProfiling.trc

Trace details

id - @traceID

status - @status

Status Conditions

0 - Stops the specified trace.
1 - Starts the specified trace.
2 -  Closes the specified trace and deletes its definition from the server.

Path - Trace file created path

Modifies the current state of the specified trace

sp_trace_setstatus @traceid , @status;

Replace the @traceid with correct id and @status with status condition and execute the above query

Let us consider @traceid - 2

Start Trace

sp_trace_setstatus 2 , 1;

Stop Trace

sp_trace_setstatus 2 , 0;

Delete Trace

1. Stop the Agent.
2. Stop the trace if it is already running.
3. Execute the below query to delete the trace from MSSQL server.

sp_trace_setstatus 2 , 2

4. Delete the trace files from the Agent Home directory.