Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server side trace to a table Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:01 PM
Points: 298, Visits: 1,102
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?
Post #1234070
Posted Wednesday, January 11, 2012 9:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 11,308, Visits: 13,096
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1234143
Posted Wednesday, January 11, 2012 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,350, Visits: 13,677
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"
Post #1234328
Posted Wednesday, August 7, 2013 4:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 5:29 AM
Points: 1, Visits: 1
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
Post #1481781
Posted Wednesday, August 7, 2013 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 11,308, Visits: 13,096
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1481808
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse