December 23, 2001 at 12:00 am
Comments posted to this topic are about the content posted at profilerloggingresultstoatable.asp
January 8, 2002 at 8:53 am
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
January 8, 2002 at 9:04 pm
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
July 23, 2003 at 10:00 pm
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
July 24, 2003 at 3:45 am
I don't recall any issues. What bug#?
Andy
July 24, 2003 at 4:34 am
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
July 24, 2003 at 5:54 pm
Could you post what you're running? I can try to repro it here.
Andy
July 26, 2003 at 1:32 am
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
July 26, 2003 at 4:34 pm
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
January 7, 2004 at 11:55 pm
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
Learn to play, play to learn !
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 but most of the time this is me
March 21, 2004 at 9:56 pm
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.
November 6, 2006 at 8:41 am
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
January 24, 2007 at 3:37 am
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.
July 13, 2007 at 8:50 am
This should answer a lot of people's questions if they have SQL 2005.
August 13, 2007 at 9:00 am
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 17 total)
You must be logged in to reply to this topic. Login to reply