Applied SQL: Server Side Traces

It’s time to get your server side trace on, with a little practical application!

I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.

Prerequisites: basic T-SQL, basic experience with SQL Profiler

Reading:

Level 1 Assignments: Generate the script for a server side trace.

[1-1] Generate the script using these options:

  • Save to file: c:\temp\Trace
  • Enable file rollover
  • Enable trace stop time.
  • In the events selection, add “DatabaseID” to all selected events.
  • Add a filter for DatabaseID = 5

[1-2] Open the script and add these queries to the bottom, commented out:

  • Add a trace stop command.
  • Add a trace delete command.
  • Add a query to get information about all existing server-side traces. (This will take just a little research.)

Level 2 Assignment: Edit the script.

[2-1] Open the generated script and make these edits: 

  • Start time should be 30 minutes after the start of the script.
  • Max file size should be 20 Mb.
  • Trace output file should be c:\temp\MyTestTraceOutput.
  • Add the option for trace file rollover.

[2-2] Find and comment out all the code that adds tracing for the “SQL:BatchCompleted” event.

Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.

Double bonus points: Do you think we should’ve done something more here? Post a related assignment of your own in the comments, and I’ll see how I do

3 thoughts on “Applied SQL: Server Side Traces”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>