SQL Server Profiler

There are 2 ways to run SQL Profiler

• Run the SQL Profiler client tool through the GUI
• Server-side tracing where events are saved to a physical file on the server by using SQL Server system stored procedures and functions.

To view the number of traces running:

SELECT count(*)
FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

To obtain more detail about the active traces:-

SELECT *
FROM :: fn_trace_getinfo(default)

To stop a trace using the traceid:-

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

status = 0 stops the trace
status = 2 closes the trace and deletes its definition from the server

A sample output :-

traceid,property,value
1,1,2
1,2,D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1504.trc
1,3,20
1,4,NULL
1,5,1


This means

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

The Default trace
The default trace great for diagnosing performance problems, finding deadlocks, and auditing security information.The default trace does not capture all trace events, but it captures key information such as auditing events, database events, error events, full text events, object creation, object deletion and object alteration. It also captures log growth events which can be invaluable to troubleshooting disk capacity problems.