How do I Trace SQL Server 2005 through restarts?

Question

At my company I need to create a Trace on our SQL Server (for a workload file), that should run for several days. However, The Server is turned off every night, so the trace dies. How do I make a Trace that will continue automaticaly on the next day?

Answer 1

Have you thought about using Server Side Traces (SST)- it's essentially rhe same thing, but without the Profiler GUI. You can start it and stop it through SQL Server jobs - and hence you can tell SQL Agent to start the SST when SQL Server starts.

They are easy to do when you get the hang of them, and you can script it all out of Profiler too!

See also:

http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_How

http://www.mssqltips.com/tip.asp?tip=1035