Share via

SQL Server Default Trace

  • Article

 

  • I've just realized how poorly documented is this great functionality that has been introduced in SQL Server 2005.

  •  

  • On the Microsoft Website, all you can find is this article (https://technet.microsoft.com/en-us/library/ms175513.aspx) and few more information.

  •  

  • Looking outside the Microsoft website, the most complete article I've found up to now, is the following:

  •  

  • Searching for a Trace

  • Solving the mystery of SQL Server 2005's default trace enabled option

  • By:  Kalen Delaney

  • https://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html

  •  

  • which I suggest you to have a look for more information.

  •  

  • Just to add my bit of experience on top of this, let me summarize some major points about this great functionality:

  •  

  • 1) What is the Default Trace in SQL 2005?

  •  

  • The default trace is nothing more than a standard server side trace which comes as default in SQL 2005.

  • As BOL states: "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur."

  • OK therefore as default SQL Server 2005 has a trace which records useful data and information of your SQL server environment, which can become very useful when troubleshooting your installation.

  •  

  • 2) Which events are logged, how long trace files are kept etc?

  •  

  • As I said, not much information can be found by searching over the internet, so you have to query the trace yourself.

  •  

  • To get the trace options:

  •  

  • SELECT * FROM ::fn_trace_getinfo(default)

  •  

  • To get the trace event details:

  •  

  • SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description

  •   FROM ::fn_trace_geteventinfo(1) t

  •     JOIN sys.trace_events e ON t.eventID = e.trace_event_id

  •     JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

  •  

  • I will avoid pasting here all the default trace events because you can retrieve them easily through the second query, but I want to explain a bit the trace options.

  •  

  • By running the firts statement:

  • SELECT *   FROM ::fn_trace_getinfo(default)

  •  

  • You will get the following:

  •  

  • 1       1       2

  • 1       2       C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_249.trc

  • 1       3       20

  • 1       4       NULL

  • 1       5       1

  •  

  • Being (from BOL):

  •  

  • Column name

    Data type

    Description

    traceId

    int

    ID of the trace.

    property

    int

    Property of the trace:

    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

    value

    sql_variant

    Information about the property of the trace specified.

  •  

  • Therefore, max size of the trace is 20MB. When the max_file_size is reached, the current trace file is closed and a new file is created. A new trace will be created also every time you restart your SQL Server instance. SQL Server will automatically keep an history of 5  traces (history of 5 is what I was able to test on my box).

  •  

  • 3) Can the trace be modified?

  •  

  • No :(

  • If you want to collect different events or data or if you want to store the trace files in an alternative location, you'll have to create your own trace.

  •  

  • Every time you will attempt to modify the default trace through setevent, you'll get the following error:

  •  

  • Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1

    The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

  •  

  • 4) Can I stop the default trace?

  •  

  • Yes, through sp_configure (it is in advanced options).

  •  

  • Question is why should you stop the default trace which is very lightweight and is used for example from some of the Standard Reports (and from the additional Performance Dashboard reports) which come with SQL 2005.

  •  

  • If you want a different trace, for whatever reason, my general advice would be to have both traces working alongside. I would eventually consider stopping the default trace in case I am experiencing performances issues and I can definitely point out that they're caused from the default trace.

  •  

  • - Beatrice Nicolini -

Comments