Server side trace to a table

Question

Server side trace to a table

sunny.tjk

SSCertifiable

Points: 5041
More actions
January 11, 2012 at 8:58 am

#251266

The link below helps me to create a server side trace to a file. I'd like to send the results to a table. Could some please tell me what change I need to make in the script to send the results out to a table?
http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_How
And also, I was told that server side tracing to a table in SQL 2000/2005 is different from SQL 2008. Is it?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can't write directly to a table with a server-side trace. Even if you could it wouldn't be wise as it would not perform as well as writing to a file and would have a negative impact on overall server performance.

You get read the trace file using fn_trace_gettable([path to file]) and use that to load the trace data into a table if you want to do that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Using fn_trace_gettable you'll need to supply the file and path and the number of files to read. As below

Load 10 files

select * into my_trace

from fn_trace_gettable('c:\mypath\mytrace.trc', 10)

Load all files

select * into my_trace

from fn_trace_gettable('c:\mypath\mytrace.trc', default)

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

"Ya can't make an omelette without breaking just a few eggs" 😉

raghu_kalwa SSC Rookie Points: 45 More actions · Answer 3

Hello Perry,

I have used the SQL server profiler,.

So can you suggest me which one is best solution to take the trace on the server .

The SQL server profiler or Server side trace ??

Thanks

Rag

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

raghu_kalwa (8/7/2013)
Hello Perry,
I have used the SQL server profiler,.
So can you suggest me which one is best solution to take the trace on the server .
The SQL server profiler or Server side trace ??
Thanks
Rag

A server-side trace has a much lower performance impact than using profiler, see this blog post by Linchi Shea, http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx and this one by Jonathan Kehayais, http://www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question