SQL Server - Enable Trace Profiler (Query Analyser)

Using SSMS Query Analyser

---- Create Trace

declare @stop datetime

set @stop = dateadd(mi,30,getdate())

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 32

exec sp_trace_create @TraceId output,2, N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\Trace2',@maxfilesize,@stop,32

-----Set Events (It's necessary to understand the TraceID and replace with 2 below.

exec sp_trace_setevent 2,41,1,1;

go

exec sp_trace_setevent 2,41,3,1;

go

exec sp_trace_setevent 2,41,6,1;

go

exec sp_trace_setevent 2,41,11,1;

go

exec sp_trace_setevent 2,41,8,1;

go

exec sp_trace_setevent 2,41,12,1;

go

exec sp_trace_setevent 2,41,13,1;

go

exec sp_trace_setevent 2,41,14,1;

go

exec sp_trace_setevent 2,41,15,1;

go

exec sp_trace_setevent 2,41,18,1;

go

exec sp_trace_setevent 2,41,35,1;

go

exec sp_trace_setevent 2,41,45,1;

go

exec sp_trace_setevent 2,41,64,1;

go

exec sp_trace_setevent 2,148,1,1;

go

exec sp_trace_setevent 2,148,3,1;

go

exec sp_trace_setevent 2,148,6,1;

go

exec sp_trace_setevent 2,148,11,1;

go

exec sp_trace_setevent 2,148,8,1;

go

exec sp_trace_setevent 2,148,12,1;

go

exec sp_trace_setevent 2,148,13,1;

go

exec sp_trace_setevent 2,148,14,1;

go

exec sp_trace_setevent 2,148,15,1;

go

exec sp_trace_setevent 2,148,18,1;

go

exec sp_trace_setevent 2,148,35,1;

go

exec sp_trace_setevent 2,148,45,1;

go

exec sp_trace_setevent 2,148,64,1;

go

exec sp_trace_setevent 2,59,1,1;

go

exec sp_trace_setevent 2,59,3,1;

go

exec sp_trace_setevent 2,59,6,1;

go

exec sp_trace_setevent 2,59,11,1;

go

exec sp_trace_setevent 2,59,8,1;

go

exec sp_trace_setevent 2,59,12,1;

go

exec sp_trace_setevent 2,59,13,1;

go

exec sp_trace_setevent 2,59,14,1;

go

exec sp_trace_setevent 2,59,15,1;

go

exec sp_trace_setevent 2,59,18,1;

go

exec sp_trace_setevent 2,59,35,1;

go

exec sp_trace_setevent 2,59,45,1;

go

exec sp_trace_setevent 2,59,64,1;

go

exec sp_trace_setevent 2,14,1,1;

go

exec sp_trace_setevent 2,14,3,1;

go

exec sp_trace_setevent 2,14,6,1;

go

exec sp_trace_setevent 2,14,11,1;

go

exec sp_trace_setevent 2,14,8,1;

go

exec sp_trace_setevent 2,14,12,1;

go

exec sp_trace_setevent 2,14,13,1;

go

exec sp_trace_setevent 2,14,14,1;

go

exec sp_trace_setevent 2,14,15,1;

go

exec sp_trace_setevent 2,14,18,1;

go

exec sp_trace_setevent 2,14,35,1;

go

exec sp_trace_setevent 2,14,45,1;

go

exec sp_trace_setevent 2,14,64,1;

go

exec sp_trace_setevent 2,15,1,1;

go

exec sp_trace_setevent 2,15,3,1;

go

exec sp_trace_setevent 2,15,6,1;

go

exec sp_trace_setevent 2,15,11,1;

go

exec sp_trace_setevent 2,15,8,1;

go

exec sp_trace_setevent 2,15,12,1;

go

exec sp_trace_setevent 2,15,13,1;

go

exec sp_trace_setevent 2,15,14,1;

go

exec sp_trace_setevent 2,15,15,1;

go

exec sp_trace_setevent 2,15,18,1;

go

exec sp_trace_setevent 2,15,35,1;

go

exec sp_trace_setevent 2,15,45,1;

go

exec sp_trace_setevent 2,15,64,1;

go

exec sp_trace_setevent 2,116,1,1;

go

exec sp_trace_setevent 2,116,3,1;

go

exec sp_trace_setevent 2,116,6,1;

go

exec sp_trace_setevent 2,116,11,1;

go

exec sp_trace_setevent 2,116,8,1;

go

exec sp_trace_setevent 2,116,12,1;

go

exec sp_trace_setevent 2,116,13,1;

go

exec sp_trace_setevent 2,116,14,1;

go

exec sp_trace_setevent 2,116,15,1;

go

exec sp_trace_setevent 2,116,18,1;

go

exec sp_trace_setevent 2,116,35,1;

go

exec sp_trace_setevent 2,116,45,1;

go

exec sp_trace_setevent 2,116,64,1;

go

exec sp_trace_setevent 2,129,1,1;

go

exec sp_trace_setevent 2,129,3,1;

go

exec sp_trace_setevent 2,129,6,1;

go

exec sp_trace_setevent 2,129,11,1;

go

exec sp_trace_setevent 2,129,8,1;

go

exec sp_trace_setevent 2,129,12,1;

go

exec sp_trace_setevent 2,129,13,1;

go

exec sp_trace_setevent 2,129,14,1;

go

exec sp_trace_setevent 2,129,15,1;

go

exec sp_trace_setevent 2,129,18,1;

go

exec sp_trace_setevent 2,129,35,1;

go

exec sp_trace_setevent 2,129,45,1;

go

exec sp_trace_setevent 2,129,64,1;

go

Additionally,

Below are the related query syntax...

EXEC sp_trace_setstatus 2, 0; --Stop trace id 2

EXEC sp_trace_setstatus 2, 1; --Start trace id 2

EXEC sp_trace_setstatus 2, 2; --delete trace id 2

Below is the query to capture the trace output to database table.

SELECT * INTO tracetable_tmp FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\Trace2_1.trc', default)

Query to check existing Traces

SELECT traceid, tracers.property, trace_properties.description, tracers.value

FROM fn_trace_getinfo(DEFAULT) AS tracers

JOIN (VALUES

     (1, 'Trace options. For more information, see @options in sp_trace_create (Transact-SQL)')

    ,(2, 'File name')

    ,(3, 'Max size')

    ,(4, 'Stop time')

    ,(5, 'Current trace status. 0 = stopped. 1 = running.')

    ) AS trace_properties(property, description) ON trace_properties.property = tracers.property

ORDER BY tracers.traceid, tracers.property;

----Second Query

SELECT status, event_count, last_event_time, * FROM sys.traces

Note:- The Default trace cannot be stopped, however, It can still be deactivated/removed using below configuration option.

sp_configure 'default trace enabled', 0;  

GO  

RECONFIGURE  

GO  

Best Regards Always

Amit Vaid

(Be Healthy......)