How to Reverse Engineer the Current Settings for sp_trace_create

  • I inherited an undocumented Production SQL Server 2005 cluster running on Windows Server 2003 from a DBA who is long gone. Apparently, the prior DBA used sp_trace_create to set up a trace that rolls over when the output file reaches its target size.

    Is there any way to reverse engineer the settings (i.e., sp_trace_create, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus, etc.) that are currently in place so that I can tweek what is being captured?

    For example, I would like to increase the number of rollover files so that they hang around on disk long enough to get backed up at night. (Something is autodeleting the files, I'm not sure what. I presume that the deletion comes from a setting used when creating the trace.)

  • Look for a Maintenance Plan that created a job that removes the .trc files:

    Management -> Maintenance Plans

    SQL Server Agent -> Jobs

    If it is a stored procedure that is doing the cleanup, you should find it under the Programmability section of your databases. Granted, you'll have to search it out, hopefully it was named well.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • select * from :: fn_trace_getinfo(0)

    I'm guessing it's the default trace, but who knows.

    the trace settings also include a maximum number of rollover files, which will delete the oldest file automatically.

    Good luck

  • SQLBOT (2/8/2010)


    select * from :: fn_trace_getinfo(0)

    The output from this query matches up with the files that are being generated on the server:

    traceid property value

    ----------- ----------- ------------------------------------------

    1 1 2

    1 2 E:\datafiles\MSSQL.1\MSSQL\LOG\log_607.trc

    1 3 20

    1 4 NULL

    1 5 1

    (5 row(s) affected)

    The SQL Server documentation for fn_trace_getinfo lists the meaning of the property column as:

    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.

    ---------------

    >>1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).

    It appears that sp_trace_create allows you to set the number of rollover files, but fn_trace_getinfo does not appear to retrieve this information. Unfortunately, I am not familiar with this aspect of SQL Server 2005. (Most of my experience is on SQL Server 2000.)

    Is there a way to test changes to the trace without risking the loss of existing functionality?

    Can an existing trace be edited? Or, does it have to be deleted and recreated?

  • From your output, only the default trace is running and there is no way to modify the settings.

    You CAN turn it off. but why?

    The default trace should be running... by default.

    :hehe:

    EXEC master.dbo.sp_configure 'show advanced options', 0;

    GO

    EXEC master.dbo.sp_configure 'default trace enabled', 0;

    GO

    RECONFIGURE WITH OVERRIDE;

    GO

    EXEC master.dbo.sp_configure 'show advanced options', 0;

    I would only turn it off if there was some sort of extreme disk space problem.

    Even then there are better ways to free up disk space.

    Thanks

  • for me, reverse engineering is to be able to script out any and all traces that exist on the server;

    the whole script like this:

    --create the trace

    exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL

    --for the Event Every SQL statement completed, capture all 64 columns of accessible data

    exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData

    exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData

    exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID

    --snip--

    --turn on the trace

    exec sp_trace_setstatus @traceidout, 1 ---start trace

    i looked in detail, and i cannot find the actual events that are set with sp_trace_setevent ; there are lookup tables for the traceeventid to it's description, but not which events were set when the trace was created;

    if you sp_helptext sp_trace_setevent , it's an internal statement, so you can't see which table it is sticking the data into, either.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • SQLBOT (2/10/2010)


    You CAN turn it off. but why?

    I must have misspoken somewhere. I do *not* want to turn off the default trace. I just need more rollover files so that they will stay on the server long enough to get backed up, and I am concerned that, if I delete the existing trace to recreate it with more rollover files, the new trace may not capture what the old trace captured. In short, I am inexperienced in this aspect of SQL 2005, and I don't want to mess up existing functionality while trying to tweek it to our needs.

  • Lowell (2/10/2010)


    for me, reverse engineering is to be able to script out any and all traces that exist on the server;

    Yes, that is the kind of output that I would like to generate for the existing trace(s), but I don't see a way to do it either. I hesitate to drop a trace to attempt a change it if I don't have the syntax for the original settings.

  • all the default trace's settings can be gleaned from these:

    select * from :: fn_trace_getinfo(1)

    select * from ::fn_trace_geteventinfo(1)

    even still, the only way you can change it is to turn it off and script your own that starts with a startup proc.

    I wouldn't over-engineer anything, though.

    How often are tape backups running?

  • Craig thank you! that was the piece could not find;

    i was locked into looking for a sys view that had the joining information...with what you posted I could get the details and build the full reverse engineering thing, that helps a lot:

    eventid EventName columnid ColumnName

    18 Audit Server Starts And Stops 6 NTUserName

    18 Audit Server Starts And Stops 7 NTDomainName

    18 Audit Server Starts And Stops 8 HostName

    18 Audit Server Starts And Stops 9 ClientProcessID

    ...

    this returns most of the information you might need, give me some time to build the whole thing; this would probably be a nice article for those who needed to reverse engineer a trace.

    select

    X.eventid,

    E.Name as EventName,

    X.columnid,

    V.name as ColumnName

    from ::fn_trace_geteventinfo(1) AS X

    INNER JOIN sys.trace_events E ON X.eventid = E.trace_event_id

    INNER JOIN sys.trace_columns V ON X.columnid = V.trace_column_id

    select

    traceid,

    property,

    value,

    CASE

    WHEN Property = 1 THEN 'Configured trace options'

    WHEN Property = 2 THEN 'Trace file name'

    WHEN Property = 3 THEN 'Max file size for the *.trc file'

    WHEN Property = 4 THEN 'Stop time for the trace session'

    WHEN Property = 5 THEN 'Current trace status (1 = On and 0 = Off)'

    END

    from :: fn_trace_getinfo(1)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • SQLBOT (2/10/2010)


    How often are tape backups running?

    Actually, this is the "root cause" of the problem. [GRUMBLE]It depends on who you ask.[/GRUMBLE] This is a locked down shop in which the DBAs do not have access to the backup configuration. We are told that backups occur every day. However, when I need to chase down some autodeleted files, the ones I need never made it to the backup tape. "Oh, there was an issue..." :crazy: I would like to make enough rollover files to survive a few days of backup "issues." :satisfied: And, yes, this sadly impacts the SQL Server backups that we write to disk.

  • Lowell (2/10/2010)


    Thanks for sharing your query. This is a keeper.

  • that's brutal.

    If it's a big concern, I'd not hesitate to robocopy the files to a fileserver and let them back it up from there.

    Cheers!

  • SQLBOT (2/10/2010)


    that's brutal.

    If it's a big concern, I'd not hesitate to robocopy the files to a fileserver and let them back it up from there.

    Agreed. We copied needed files elsewhere at other shops, but this shop does not allow any file shares accessible from the server.

  • Nice work, Lowell.

    post it back here if you ever finish it up to write the trace start script.

    It's more useful the way it is, though. You can acually see the event name.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply