Need help writing script to run profiler on the server.

Question

I need to create a single trace file that spans several days for one of my databases. This server also restarts every night.

I was told that I should create a script to start the trace every time the server starts but i've been having trouble finding information on just what my script should look like to accomplish what it needs to do. This is the only resource I've come across for the sp_trace_create stored proc.

Can anyone help me come up with a script that will create a trace file that spans several days with the server restarting? My end goal is to pass this trace file to the DTA to get some suggestions for indexing.

Answer 1

Define the trace in SQL Profiler, then script out the trace definition (File/Export/Script Trace Definition), see How to: Create a Transact-SQL Script for Running a Trace (SQL Server Profiler).

the server restart part is a bit trickier though. You can either modify the default trace (I would strongly recommend against it), you can create a SQL Agent job to create the script, or you could start the trace from a auto-start-up procedure, see Automatic Execution of Stored Procedures. I would use auto-start-up procedures