Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Greg Drake,
What does this script do?
This script loads temporary stored procedures that assist with server-side tracing. Its purpose is to make server-side tracing easier to perform, faster to implement, and generally less intimidating.
Why should I use this script?
How do I use it?
In short, you execute the full script to load the temp procs and then go to the bottom of the script to use the pre-written EXEC statements. However, this overlooks the preparation required to load your own trace events. Please see the instructions in the comments at the head of the script.
Where can I learn about server-side traces?
This script doesn't excuse the user from being familiar with the use of server-side traces, or tracing in general. Please educate yourself on the subject to get the most out of it. The following set of articles is a great place to start.
Stairway to Server-side Tracing
Why did you make it?
My job requires that I perform Production support on many databases that are located on clients' servers. When troubleshooting issues in a Production environment, server-side trace is the way to go because it requires no extra software running and has negligible performance impact. However, I have always found the SQL Server built-in procedures for managing these traces to be difficult to work with and deploy quickly. With the client looking on, I really want to be able to quickly and confidently perform all the actions required to manage my traces.
I started out by developing scripts to act as wrappers for the built-in functions. Scripts are usually nice because they don't require me to install new objects on a client's server. But these scripts got big and there were several required so I ended up fumbling around between multiple SSMS tabs or within a long script. That led me to turn the individual scripts into temp procs so I could work primarily in a compact EXEC area. As a bonus, using temp procs allowed me to separate the reusable code and that gave me a mini-framework with which to integrate my trace-related queries as lookup procs.
How to Script Traces for SQL and Analysis Services
Script to help you analyze a trace to find worst performing procs.
Script to remove old trace files
script to delete old trace files