Need to view the history of sys.trace files

KARUTHAPPANDYAN MAHALINGAM 1 Reputation point
2021-09-07T11:14:43.49+00:00

Hi Team,

We have deployed the sql traces in our SQL RDS instances and its created a multiple log files and we need to find a history of log file names and other details for our requirement.

--select path from sys.traces
D:\rdsdbdata\log\RDSSlowTrace_3.trc

Eg :
D:\rdsdbdata\log\RDSlowTrace_1.trc
D:\rdsdbdata\log\RDSSlowTrace_2.trc

Requesting your support to identify the history of trace files and its locations.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2021-09-07T13:16:10.467+00:00

    Requesting your support to identify the history of trace files and its locations.

    SQL Server don't store a trace history, you can only get the current traces with 

    select *
from sys.traces
    0 comments
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-09-07T21:33:36.777+00:00

    These trace files will all be in the same location with a number tacked on in the end as in your example. To my knowing, SQL Server does not record elsewhere that a trace rolls over. How many rollover files that retained depends on what you said when you set up the trace.

    0 comments
  3. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-08T03:25:33.053+00:00

    Hi @KARUTHAPPANDYAN MAHALINGAM ,

    Agree with them.
    Here is a MS doc: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/default-trace-enabled-server-configuration-option?view=sql-server-ver15

    Note: The extended event was introduced from SQL 2008. It is a highly extended tool for performance monitoring and fault detection. It is used to replace SQL Trace, which is not recommended since SQL 2012. It is lightweight and rich in events, and can meet many tasks that cannot be achieved or costly in SQL Trace at low cost.

    Best regards,
    Seeya

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments