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:
- SQLServerPedia article: The Server-side Trace: What, Why, and How (note that what I wrote about @option is incorrect; you do NOT want to enable “shutdown on error”).
- SQL Server Books Online sp_trace_create, sp_trace_setevent, and fn_trace_getinfo articles (just for a general overview, and for later reference).
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
