SQL Profiler Trace

-- STOP TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 0

-- START TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 1

-- CLOSE & DELETE TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 2

-- INFO ABOUT A TRACE

-- ALL TRACES

select *

from fn_trace_getinfo (NULL)

select *

from fn_trace_getinfo (0)

select *

from fn_trace_getinfo (DEFAULT)

-- INFO OF SPECIFIC TRACE_ID

select *

from fn_trace_getinfo (3)

-- IMPORT TRACE FILE INTO A TABLE

SELECT * INTO yourtracetable

FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 500

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

exec @rc = sp_trace_create

      @TraceID output,

      @options = 2,           -- TRACE_FILE_ROLLOVER

      @tracefile = N'Z:\MSSQL10.MSSQLSERVER\MSSQL\SQLTraces\AdvancedReportsDecommission',

      @maxfilesize = @maxfilesize, -- MB

      @stoptime = NULL, -- date and time the trace will be stopped

      @filecount = 10         -- maximum number or trace files to be maintained with the same base filename

-- FIND THE SOURCE OF A TRACE

select *

from sys.traces