SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Profiler - Logging Results to a Table / Latest Posts

RE: Profiler - Logging Results to a Table

Ranga N — Wed, 24 Mar 2010 12:38:39 GMT

[quote][b]Dinesh Priyankara (7/24/2003)[/b][hr]<font face='Verdana'>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 1File '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</font id='Verdana'>MCP MCSE MCSD MCDBA[/quote]The fn_trace_gettable function looks for the trace file N'E:\Traces\monitor.trc' on the sql server you are connected in the management studio. For example if the trace file E:\Traces\monitor.trc exists on the SQL Server "SQLSERVerTest", connect to this SQL server thru mgmt studio and then try to query.

RE: Profiler - Logging Results to a Table

Krop — Mon, 25 Jan 2010 13:14:17 GMT

When you create the trace, don't add ".trc' to the end of the name as sp_trace_create will also add it.exec sp_trace_create @traceid = @traceid OUTPUT, @options = 0, @tracefile = 'C:\Temp\myTraceFile', -- DO NOT ADD .TRC HERE! @maxfilesize = 10, @stoptime = 0SELECT * FROM ::fn_trace_gettable('C:\Temp\myTraceFile.trc', default)If you have already added it, just amend the filename from "myTraceFile.trc.trc" to "myTraceFile.trc" or you'll only be able to open it using the Profiler GUI and not using fn_trace_gettable.Rgds,Krop

RE: Profiler - Logging Results to a Table

ALZDBA — Mon, 20 Aug 2007 07:57:00 GMT

If you want to trace in batch, you need to trace to a file !

Afterward you can load the file to a given (empty) table using :

-- HOW TO: Programmatically Load Trace Files into Tables-- http://support.microsoft.com/kb/270599-- SELECT * INTO myserver_mydb_CLOR_070322 FROM ::fn_trace_gettable('F:\myTraces\trace_myserver_mydb_070322.trc', 32)

RE: Profiler - Logging Results to a Table

Michael-401546 — Mon, 13 Aug 2007 09:00:00 GMT

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

RE: Profiler - Logging Results to a Table

Jon Mitchell-464369 — Fri, 13 Jul 2007 08:50:00 GMT

This should answer a lot of people's questions if they have SQL 2005.http://msdn2.microsoft.com/en-us/library/ms345134.aspx

RE: Profiler - Logging Results to a Table

Ian Cannonier — Wed, 24 Jan 2007 03:37:00 GMT

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.

RE: Profiler - Logging Results to a Table

AndrewM-375946 — Mon, 06 Nov 2006 08:41:00 GMT

To setup a trace that only views the last hour but does not run out of space requires the following:15:00JOB1-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.trc16:01JOB 2 Step 1- DROP current table where trc was importedstep 2- use trace_table to import trc file

RE: Profiler - Logging Results to a Table

Grant Cribb — Sun, 21 Mar 2004 21:56:00 GMT

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.

RE: Profiler - Logging Results to a Table

ALZDBA — Wed, 07 Jan 2004 23:55:00 GMT

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 ?

RE: Profiler - Logging Results to a Table

mccork — Sat, 26 Jul 2003 16:34:00 GMT

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

RE: Profiler - Logging Results to a Table

Dinesh Priyankara — Sat, 26 Jul 2003 01:32:00 GMT

This is what I did.creation and starting:------------------------------------------declare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @maxfilesize = 5 exec @rc = sp_trace_create @TraceID output, 2, N'E:\Traces\monitor', @maxfilesize, NULL declare @on bitset @on = 1exec sp_trace_setevent @TraceID, 54, 1, @onexec sp_trace_setevent @TraceID, 54, 3, @onexec sp_trace_setevent @TraceID, 54, 6, @onexec sp_trace_setevent @TraceID, 54, 9, @onexec sp_trace_setevent @TraceID, 54, 10, @onexec sp_trace_setevent @TraceID, 54, 11, @onexec sp_trace_setevent @TraceID, 54, 12, @onexec sp_trace_setevent @TraceID, 54, 13, @onexec sp_trace_setevent @TraceID, 54, 14, @onexec sp_trace_setevent @TraceID, 54, 16, @onexec sp_trace_setevent @TraceID, 54, 17, @onexec sp_trace_setevent @TraceID, 54, 18, @ondeclare @intfilter intdeclare @bigintfilter bigintset @intfilter = 8exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilterexec 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

RE: Profiler - Logging Results to a Table

Andy Warren — Thu, 24 Jul 2003 17:54:00 GMT

Could you post what you're running? I can try to repro it here.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Profiler - Logging Results to a Table

Dinesh Priyankara — Thu, 24 Jul 2003 04:34:00 GMT

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 1File '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.DineshMCP MCSE MCSD MCDBA

RE: Profiler - Logging Results to a Table

Andy Warren — Thu, 24 Jul 2003 03:45:00 GMT

I don't recall any issues. What bug#?Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Profiler - Logging Results to a Table

Dinesh Priyankara — Wed, 23 Jul 2003 22:00:00 GMT

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 1File '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.DineshMCP MCSE MCSD MCDBA

RE: Profiler - Logging Results to a Table

Andy Warren — Tue, 08 Jan 2002 21:04:00 GMT

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

RE: Profiler - Logging Results to a Table

jG — Tue, 08 Jan 2002 08:53:00 GMT

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

Profiler - Logging Results to a Table

Andy Warren — Sun, 23 Dec 2001 00:00:00 GMT

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