Sql-server – How to Get All Server Side Traces in SqlServer from SSMS, stop it from SSMS and delete the log files

sql serverssmstrace

How to Get All Server Side Traces in SqlServer from SSMS(Active,Inactive), stop it from SSMS and delete the log files.
I need all traces saving into file as well as table.

I tried SELECT * FROM fn_trace_getinfo(DEFAULT). Some times few parameters are NULL. What does it mean ?

Best Answer

Trace properties with NULL values indicate options were not specified when the trace was created. Properties are listed in the fn_trace_getinfo documentation and can be included a query for easy reference:

SELECT traceid, trace.property, trace_properties.description, trace.value
FROM fn_trace_getinfo(DEFAULT) AS trace
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 = trace.property
ORDER BY trace.traceid, trace.property;

Rowset traces (Profiler or SMO) will not have values pertaining to server-side traces, such as file name, max size, etc. Below are example results of the above query including the default trace and a running Profiler trace.

+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
| traceid | property |                                     description                                     |                                      value                                      |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
|       1 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 2                                                                               |
|       1 |        2 | File name                                                                           | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc |
|       1 |        3 | Max size                                                                            | 20                                                                              |
|       1 |        4 | Stop time                                                                           | NULL                                                                            |
|       1 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
|       2 |        1 | Trace options. For more information, see @options in sp_trace_create (Transact-SQL) | 1                                                                               |
|       2 |        2 | File name                                                                           | NULL                                                                            |
|       2 |        3 | Max size                                                                            | NULL                                                                            |
|       2 |        4 | Stop time                                                                           | NULL                                                                            |
|       2 |        5 | Current trace status. 0 = stopped. 1 = running.                                     | 1                                                                               |
+---------+----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+

The trace properties are also exposed as normalized columns in sys.traces. This DMV includes all legacy SQL Traces (but not Extended Event traces) regardless of whether its a server-side trace to a file or rowset trace returning data to a Profiler client. You may find it easier to query sys.traces instead. Example output from SELECT * FROM sys.traces.

+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
| id | status |                                      path                                       | max_size | stop_time | max_files | is_rowset | is_rollover | is_shutdown | is_default | buffer_count | buffer_size | file_position | reader_spid |       start_time        |     last_event_time     | event_count | dropped_event_count |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+
|  1 |      1 | D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_175.trc | 20       | NULL      | 5         |         0 |           1 |           0 |          1 |            2 |        1024 | 1048576       | NULL        | 2020-05-20 07:44:55.487 | 2020-05-24 08:25:08.983 |        1088 | NULL                |
|  2 |      1 | NULL                                                                            | NULL     | NULL      | NULL      |         1 |           0 |           0 |          0 |         1088 |           1 | NULL          | 94          | 2020-05-24 08:18:00.503 | 2020-05-24 08:25:08.983 |          74 | 0                   |
+----+--------+---------------------------------------------------------------------------------+----------+-----------+-----------+-----------+-------------+-------------+------------+--------------+-------------+---------------+-------------+-------------------------+-------------------------+-------------+---------------------+

EDIT:

A trace can be stopped and deleted in SSMS by executing sp_trace_setstatus from a query window, specifying the desired trace id and status (0 = stop, 2 = delete). For example, the rowset trace with traceid 2 above can be stopped and deleted with:

EXEC sp_trace_setstatus 2, 0; --stop trace id 2
EXEC sp_trace_setstatus 2, 2; --delete trace id 2
Related Question