0

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 ?

1 Answer 1

1

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
4
  • Irrespective the trace is created in profiler,server it will be displayed in sys.traces ? Also How to stop a trace from SSMS ? May 25, 2020 at 8:50
  • @abhaysreekumar, I edited my answer to address your additional questions. Note trace files will need to be deleted separately.
    – Dan Guzman
    May 25, 2020 at 10:50
  • Thank you very much for the response. sys.traces does not include Extended Events Traces. So How can we find Extended Events Traces? May 26, 2020 at 9:43
  • Extended Event traces are exposed with the Extended Event DMVs. Unlike deprecated SQL Traces, XE traces are managed with DDL.
    – Dan Guzman
    May 26, 2020 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.