Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

Server-Side Trace Pack

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?

  • It allows you to reference traces by a single name for all actions. You won't have to reference a trace_id (unless you want to) and you won't have to use a '.trc' extension for some actions and not others.
  • It simplifies the parameters you would need to use with the built-in SQL Server procedures. These ones are more intuitive and shouldn't require double-checking the online specifications.
  • The use of temp stored procs keeps you from cluttering up the server. Once your session is closed (same as closing the tab in SSMS), the proc definitions are cleared.
  • It provides pre-written queries in the lookup procs with filtering parameters and plenty of extra details. These can help you review your own traces or inspect other traces on the server you aren't familiar with.
  • It minimizes the actions required for running traces to only 'create', 'start', 'stop', and 'close'. Note that preparation is usually required to load trace events beforehand.

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.

Total article views: 936 | Views in the last 30 days: 6
 
Related Articles
ARTICLE

Scripting Trace's for SQL 2005

How to Script Traces for SQL and Analysis Services

FORUM

trace

trace

SCRIPT

Trace Analysis script

Script to help you analyze a trace to find worst performing procs.

FORUM

Script to remove old trace files

Script to remove old trace files

FORUM

script to delete old trace files

script to delete old trace files

Tags
auditing    
default trace    
monitoring    
production support    
profiler    
server side trace    
sql server 2005    
sql trace    
trace    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones