Profiler - Logging Results to a Table

  • Andy Warren

    SSC Guru

    Points: 119684

    Comments posted to this topic are about the content posted at profilerloggingresultstoatable.asp

  • jG

    SSCrazy

    Points: 2505

    From my experience; is'nt it optimal to send the results to a file, then save it to a SQL table? Seems profiler writes faster to a file than a table. -JG


    -JG

  • Andy Warren

    SSC Guru

    Points: 119684

    Maybe! Havent timed honestly, though you have to figure if you have indexes on the table that might add to a bit more time than just appending to the end of a text file (basically anyway). Whether the load/time matters depends on the volume, hardware, what you're logging etc. I just think it would be nice to log directly to a table - after all, SQL is ALL about tables. Especially for a quick perf tune type scenario it would be handy.

    More importantly, I'd like to have the option. Stick warning labels on it if you must, but why not let us do it?

    Of course once you know the deal its not bad, so the intent here was to maybe save someone new to Profiler a few minutes of head scratching...and let me air my complaint too!

    Thanks for reading the article and taking time to comment.

    Andy

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    Hi Andy,

    It is today I read this article. I am having a problem with fn_trace_gettable. I have created my trace through sp_trace_create with option 2.

    Error is :

    Server: Msg 567, Level 16, State 2, Line 1

    File 'E:\Traces\monitor.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

    I surfed on various sites and found in microsoft that says this is known bug in SQL Server.

    Did you get this error? Or you know how to overcome this.

    I can read this if I stop the server and restart. Untill restart, file size remain 0.

    Please reply soon on this 'cause I have to create the trace through SPs.

    Dinesh

    MCP MCSE MCSD MCDBA

  • Andy Warren

    SSC Guru

    Points: 119684

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    Ok. What I want is : create a trace using sp_trace_create with option 2. That's what I have done. But I cannot read the tracefile untill I stop and restart the server. I am getting an error when I call fn_trace_gettable function.

    <b>

    SELECT * FROM :: fn_trace_gettable (N'E:\Traces\monitor.trc', 1)

    Output:

    Server: Msg 567, Level 16, State 2, Line 1

    File 'E:\Traces\monitor.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.[/b]

    As per microsoft site http://support.microsoft.com/support/kb/articles/q273/9/72.asp , This is bug. And they have given a workaround but no luck.

    I highly appreciate if you can help me on this.

    Dinesh

    MCP MCSE MCSD MCDBA

  • Andy Warren

    SSC Guru

    Points: 119684

    Could you post what you're running? I can try to repro it here.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Dinesh Priyankara

    SSCommitted

    Points: 1681

    This is what I did.

    creation and starting:

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

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    set @maxfilesize = 5

    exec @rc = sp_trace_create @TraceID output, 2, N'E:\Traces\monitor', @maxfilesize, NULL

    declare @on bit

    set @on = 1

    exec sp_trace_setevent @TraceID, 54, 1, @on

    exec sp_trace_setevent @TraceID, 54, 3, @on

    exec sp_trace_setevent @TraceID, 54, 6, @on

    exec sp_trace_setevent @TraceID, 54, 9, @on

    exec sp_trace_setevent @TraceID, 54, 10, @on

    exec sp_trace_setevent @TraceID, 54, 11, @on

    exec sp_trace_setevent @TraceID, 54, 12, @on

    exec sp_trace_setevent @TraceID, 54, 13, @on

    exec sp_trace_setevent @TraceID, 54, 14, @on

    exec sp_trace_setevent @TraceID, 54, 16, @on

    exec sp_trace_setevent @TraceID, 54, 17, @on

    exec sp_trace_setevent @TraceID, 54, 18, @on

    declare @intfilter int

    declare @bigintfilter bigint

    set @intfilter = 8

    exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

    exec sp_trace_setstatus @TraceID, 1

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

    Did some transactions and tried to load data with function:

    select * from :: fn_trace_gettable( N'E:\Traces\monitor.trc',default)

    output: error, what I mentioned.

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

    check the file but size is still o KB.

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

    stopped the SQL Server:

    check the file, now the size is 128KB.

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

    restarted the SQL Server:

    run the function again:

    got the result.

    What's wrong with code?

    MCP MCSE MCSD MCDBA

  • mccork

    SSC-Insane

    Points: 22288

    Dinesh,

    Is your problem possibly that you're trying to read the trace file while it's still open and Profiler is writing to it? My understanding is that you can only read it if a) the trace is stopped, or b) if the rollover threshold has been reached and the trace has closed the file and started a new one.

    Rather than stopping SQL Server, I usually use a time threshold and/or a max file size threshold (with no rollover) or sp_trace_setstatus to stop non-interactive traces.

    Edited by - mccork on 07/26/2003 4:34:58 PM


    Cheers,
    - Mark

  • Johan Bijnens

    SSC Guru

    Points: 134282

    Nice article.

    What I would like to do is to profile a sqlservers activities, store that trace-info in a table on another sqlserver and viewing only the latest hour of activity in profiler itself. I've got this problem that occurs every once in a while, wich I want to repro, but can't get a clue.

    When I run profiler, it stops after a day or so, because the computer which runs profiler runs out of space on it's C-drive.

    Any ideas ?

     

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Grant Cribb

    Grasshopper

    Points: 22

    On a different thought:

    I am in the process of auditing security events using a profiler scripts with the output going directly to a file (for all the reasons previously mentioned in this forum).

    The problem that I am running across is that the output to the trace file is being buffered (128K) and if a nasty outage occurs before the buffer is flushed (power supply, blue screen, whatever)then the audited security trace is being lost.

    I am new to SQL Server 2000, is there something obvious that I have missed (e.g. bypass buffered writes)?

    Any tips would be appreciated.

  • AndrewM-375946

    Right there with Babe

    Points: 717

    To setup a trace that only views the last hour but does not run out of space requires the following:

    15:00

    JOB1-Step 1- The trace script (set to stop after an hour) approx 300Mb, with trc file saved with DATETIME stamp, i.e. 6NOV 2006 1500PM.trc

    16:01

    JOB 2 Step 1- DROP current table where trc was imported

    step 2- use trace_table to import trc file

  • Ian Cannonier

    Grasshopper

    Points: 11

    I have been asked to do a similar task to Grasshopper.

    I have an MS Analysis Server which processes a cube in the early hours of the morning. Some times it takes an hour and others 4. I have been asked to provide an automated trace which saves the results to a table, so they can be reviewed later to find out why. However, I can not find a way to do this without writing the trace to a flat file first.

    I like the idea of creating a similar function to that of Profiler so it can be reused with ease, but which tables / views would I apply the triggers too?

    Any help would be gratefully received.

  • Jon Mitchell-464369

    Newbie

    Points: 3

    This should answer a lot of people's questions if they have SQL 2005.

    http://msdn2.microsoft.com/en-us/library/ms345134.aspx

  • Michael-401546

    Mr or Mrs. 500

    Points: 557

    OK,  one last time.   Has anybody been able to trace to a table?  Andy, in all these years since your article, were you able to get it working?  I am stuck with 2000 for the time being

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

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