Home » Applied SQL »Beginner »Intermediate »sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

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


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

Currently there are "3 comments" on this Article:

  1. [...] Applied SQL: Server Side Traces – It’s been a four day working week here in the UK, so most of us have been scrambling to get things done but if you just can’t get enough SQL Server action and feel the need to flex those brain muscles some more then Jen McCown (Blog|Twitter) has got an assignment for you. [...]

  2. [...] McCown’s (blog | twitter) third assignment in the Applied SQL series is all about setting up a Server Side Trace with SQL [...]

  3. Jay Bonk says:

    Hey Jen,

    Completed the assignment on Server Side Traces


Comment on this Article:

Release Date: Minion by MidnightDBA

Just 21 hours 3 minutes 57 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/