Welcome to Sign in | Help

SQL Server 2005 - The Default Trace

  •  10-08-2007, 7:33 PM

    SQL Server 2005 - The Default Trace

    What's in a name? That which we call this trace by any other word would mean it’s stopped? But nay, it’s running quietly on your server and I will tell you why.

    The Default Trace is a light-weight trace that is running by the default on your SQL Server. Its primary role is to help you in troubleshooting various problems. Light-weight means that you shouldn't be too concerned about its impact on your server’s performance.

    Let’s get some more information about it. Using the fn_trace_getinfo function, you can get some of its properties. The Default Trace has the id value 1 so I will call the function fn_trace_getinfo using 1 as argument.

    SELECT *
    FROM fn_trace_getinfo(1)

    traceid property value
    ------- -------- ----------------------------------------
    1       1        2
    1       2        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_156.trc
    1       3        20
    1       4        NULL
    1       5        1

    I will explain the results, don’t worry! The property column has the following meaning: 1= Trace options, 2 = File name, 3 = Max size, 4 = Stop time, 5 = Current trace status. In our case, we see a rollover trace having a maximum size of 20MB. Rollover means that when the maximum size of the file is reached a new file will be created. The number appended to the name of the trace (156 in this case) is used to indicate its sequence.  There are only 5 trace files just in case you wonder if it will fill-up your disk drive’s space. The trace file location can be different on your server (and you can see the exact location using the above query).

    The next step is to see the events recorded by this trace: 

    SELECT T.eventid AS [id], E.name
    FROM fn_trace_geteventinfo(1) T
      JOIN sys.trace_events E
      ON T.eventid = E.trace_event_id
    GROUP BY T.eventid, E.name

    -- Results
    id    name
    ----- -----------------------------
    18    Audit Server Starts And Stops
    20    Audit Login Failed
    22    ErrorLog
    46    Object:Created
    47    Object:Deleted
    55    Hash Warning
    69    Sort Warnings
    79    Missing Column Statistics
    80    Missing Join Predicate
    81    Server Memory Change
    92    Data File Auto Grow
    93    Log File Auto Grow
    94    Data File Auto Shrink
    95    Log File Auto Shrink
    102   Audit Database Scope GDR Event
    103   Audit Schema Object GDR Event
    104   Audit Addlogin Event
    105   Audit Login GDR Event
    106   Audit Login Change Property Event
    108   Audit Add Login to Server Role Event
    109   Audit Add DB User Event
    110   Audit Add Member to DB Role Event
    111   Audit Add Role Event
    115   Audit Backup/Restore Event
    116   Audit DBCC Event
    152   Audit Change Database Owner
    153   Audit Schema Object Take Ownership Event
    155   FT:Crawl Started
    156   FT:Crawl Stopped
    157   FT:Crawl Aborted
    164   Object:Altered
    167   Database Mirroring State Change

    The names of the events are self-explanatory. You can see that memory changes will be recorded as well as DBCC commands execution, DDL events (creating or dropping objects, schema modifications) etc.

    But where you can see the recorded information? You will find the answer to this question looking at the Summary Reports in SQL Server Management Studio. To view these reports, start SQL Server Management Studio, press F7 (or click Summary in the View menu), and finally use the Reports dropdown list to select a report.

    As you may expect, you can get the same data by running a SQL query:

    SELECT *
    FROM fn_trace_gettable
    ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)

    I won’t include the results because they are pretty large and they don't look as nice as the graphical reports in Management Studio. I will recommend you to try the scripts above and to test-drive the new Summary Reports, I can tell you that it’s worth it. They can give you a good idea of what happens or happened on your server from object creation to configuration changes. In the end, the Default Trace along with the Summary Reports and the new Dynamic Management Views will make your life easier (provided you work with SQL Server of course).


    [published in Culminis Compass http://emea.culminis.com/Newsletters/200603_march/0306defaulttrace.htm]

    Cristian Andrei Lefter, SQL Server MVP
    MCTS, MCITP - Database Administrator SQL Server 2005
    Filed under:
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems