August 14, 2008 at 1:16 pm
I see that using profiler you can send your trace data to a table instead of a file.
However, when creating a server side trace with sp_trace_create: http://msdn.microsoft.com/en-us/library/ms190362(SQL.90).aspx I don't see any option to specify a table.
I would very much like to specify a table to have the trace data sent to, NOT a file.
Is this possible with a server side trace? Thank you.
August 14, 2008 at 1:23 pm
I have never been able to figure this out, so I am interested in knowing as well, as I have always had to put it to disk. So I ended up putting it to files and then I have a job that uses the log reader to read multiple logs and bring it into a table on a regular basis. So its not real time, but still gets into a table.
August 14, 2008 at 1:27 pm
Do you then truncate the log file on disk or somehow delete it so you don't have duplicate data floating around?
Though, it still seems like it should be possible to dump it straight to a table... 🙁
Profiler does that, but then perhaps profiler just retrieves the data directly from the server, then shunts it directly to a table on whatever server you selected.
Still though, if profiler can receive the data as a data stream and not as a file going to disk... why can't a server side trace do the same and just direct it to a table and not a file 🙁
August 14, 2008 at 1:40 pm
Server-side trace is only to a file. Tracing to a table isn't recommended even from profiler GUI because it has the highest impact on the traced server of all of the options.
What I would suggest is trace to a file and then have a job that runs after the trace has finished and automatically loads the trace into a table using the fn_trace_gettable function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 1:47 pm
So then in that case it sounds like I'd need the job to run on a regular basis (or be triggered) and then scoop up what was in the file using that sproc and dump it to a table.
I assume I'd also need to know the name of the trace file so that the sproc knows which file to get and load into the table?
So I'd need something to start the trace, provide a file name, and then retain that filename somewhere until the trace finished under whatever conditions exist to end the trace.
Then take that file name, pass it to the sproc that loads the file into a table and then clean/delete the trace log file off the hard drive once it finished loading it to a table.
August 14, 2008 at 1:48 pm
Second the motion. Save to a file, import the file.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 1:54 pm
Profiler actually uses a trace file locally. If you watch the %temp% directory, you can see it created as Profiler starts up. Not sure how it writes the file locally, but I know that it does.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 1:55 pm
Pretty much, although you can interrogate SQL as to the traces that are running.
select * from fn_trace_getinfo(0)
Get profiler to generate the trace creation code for you. It's easier than writing it by hand. File-export-generate trace definition (or something like that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 2:02 pm
GilaMonster (8/14/2008)
Pretty much, although you can interrogate SQL as to the traces that are running.
select * from fn_trace_getinfo(0)Get profiler to generate the trace creation code for you. It's easier than writing it by hand. File-export-generate trace definition (or something like that)
Wait, can that run on an ACTIVE trace file or do I have to wait for the trace to finish, THEN run that?
If it DOES run on an active trace file, then does it somehow truncate or delete the data in the active file after it moves it to a table (or is there someway to do that?) Otherwise it seems that if I ran it while the trace was active and then ran it again I'd get duplicate data moved to the table.
August 14, 2008 at 2:10 pm
I think I haven't explained clear enough.
fn_trace_getinfo shows you the active traces that SQL knows about. It just shows traceID, status (stopped, paused, running) and file location that the trace is being written to.
fn_trace_gettable is used to read the file of a stopped trace (or at least a file that isn't being written to) and to return the results as a recordset
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply