Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I set up a server side trace that dumps to a database instead of a trace file?

I set-up and tested a trace configuration I want with Sql Server Profiler that dumps only needed info straight to a separate database (without a .trc file).
When I export this trace as sql statement however, I see it is configured to save to .trc file instead of a database.

Either my googling-fu is letting me down or nobody is interested in this.

share|improve this question
add comment

2 Answers

What events do you need?

Many trace events are available in event notifications and event notifications can dump the EVENDATA() as Service Broker messages into your database of choice, in real time.

share|improve this answer
    
Interesting. Should look into that too. What I had in mind is a permanent trace server side on our major database servers to have an eye on our heaviest/most executed queries. If I could get this going with little to no payload overhead, I would like to run this on our production database servers. –  Boris Callens Apr 23 '13 at 7:34
    
I had a look at this: msdn.microsoft.com/en-us/library/ms190191(v=sql.105).aspx, but from the comparison I don't see why I would want event notifications over traces. –  Boris Callens Apr 23 '13 at 8:10
    
You want EN if you need real time reaction (eg. email notification). Traces are much more lightweight, but do not provide any possibility to react. There are also aggregation scenarios (remote delivery to central warehouse) that EN is much better at. Think things like deadlocks, db growth, blocked processes. But definitely I would not consider EN for high frequency events like StmtCompleted. –  Remus Rusanu Apr 23 '13 at 8:20
    
I see, thanks for your answer. I can keep that in mind for later. My current use case however (finding query timeouts, top 10 most expensive queries) seems to call for traces. –  Boris Callens Apr 23 '13 at 8:27
1  
Have you looked at sys.dm_exec_query_stats? –  Remus Rusanu Apr 23 '13 at 8:30
show 1 more comment

I highly doubt no one is interested! :-)

Here's my link collection that I used to get into the topic:

Hope those help you as well !

Update:

Ok, to clear things up:

  • in order to create a server-side trace, you need a trace definition script
  • to create that trace definition script, the easiest approach is to use SQL Server Profiler to define what you want to trace - which events, what properties
  • you then use SQL Server Profiler to create that server-side trace definition script for you (doing it manually, from scratch, is a major hassle)
  • but then, to execute - you don't use SQL Server Profiler (that's putting too much load on your server), but you just create and run the trace on the SQL Server
share|improve this answer
    
I highly doubted that too ;) Thanks, I will run through your links. –  Boris Callens Apr 19 '13 at 9:03
    
Call me a moron, but (except for the sqlservercentral article which is behind a pay wall) all these articles seem to either point to trace to a file or trace with the sql profiler tool. Am I missing something? –  Boris Callens Apr 19 '13 at 9:26
    
@BorisCallens: I don't think the SQL Server Central thingie is a paywall - you just need to register (for free). Also: all those articles show how to use the SQL Profiler to define a SQL Server trace - this is really the same thing - you're just not running the trace "live" using SQL Profiler, but you have SQL Server run it as a "server task", so to speak. –  marc_s Apr 19 '13 at 9:40
1  
Hey marc_s. Thanks for the update. Creating the trace definition script with profiler to then run as a server side trace was indeed what I wanted to do. But all the generated scripts and examples I see, dump their data to a trace file instead of directly to the database. That is what I'm puzzled about. –  Boris Callens Apr 23 '13 at 7:32
1  
@BorisCallens: dumping into trace files IS the preferred way to go! This puts less load on your (production) server than writing directly into the database. Dump the trace into files, and then go grab those files onto your local test environment, and analyse them there! Put as little load as possible on your production machines... –  marc_s Apr 23 '13 at 8:07
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.