Here is a fantastic post with a detailed look at how to create stored procedures to automate the process of creating server-side traces in SQL Server.

The post contains instructions on creating wrapper sprocs for the trace functions, and a nice list of scenarios on how to use them, including:

• Scenario 1: Identifying long running stored procedures
• Scenario 2: Get a list of all the stored procedures called within a specific database:
• Scenario 3: Tracing for specific errors
• Scenario 4: Troubleshooting deadlocks
• Scenario 5: Identifying stored procedure recompilations
• Scenario 6: Starting a Blackbox trace

Those who attend my SQL Server 2005 performance tuning workshops  (MOC 2784 and 2790) will find this a useful followup to the modules on using Profiler to trace performance.