0

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.

2 Answers 2

1

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.

6
  • 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. Apr 23, 2013 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. Apr 23, 2013 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. Apr 23, 2013 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. Apr 23, 2013 at 8:27
  • 1
    Have you looked at sys.dm_exec_query_stats? Apr 23, 2013 at 8:30
1

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
6
  • I highly doubted that too ;) Thanks, I will run through your links. Apr 19, 2013 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? Apr 19, 2013 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, 2013 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. Apr 23, 2013 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, 2013 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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