SQL Server: Script to Start and Stop a New Trace without Using Profiler GUI

SQL Server Profiler is an useful tool, but basically, important part of profiler is trace which this graphical interface shows. Trace data can be collected and saved with in a file, even without using SQL Server Profiler graphical interface.

To do so you need a script that will perform this task. Good thing is that, SQL Server Profiler helps us to create this script, through following path.

Now, only you need to change it according to your requirements. Here is a complete script for auto trace which we use on production servers, to capture quires taking time more than 5 seconds to execute. Script can be used in a job to execute on daily bases. 

 

To stop this auto trace, you just need to change its status to STOP and then CLOSE. Keep in mind to close a trace, you must stop it first.