Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---

• Connect to SSMS
• Go to Tools
• Go to SQL Profiler
• Chose the appropriate trace template according to requirements ( or create your own)
• Check enable trace stop time and specify the date and time that you want it to stop
• Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime

 

Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

SQL server side traces handy commands

select * from ::fn_trace_getinfo(0)    //list all the traces--where property = 5
--stops the tracet of raceid 2 EXEC sp_trace_setstatus 2 ,0

---restart the trace of traceid 2
EXEC sp_trace_setstatus 2 ,1
--deletes the trace of traceid 2 from the server EXEC sp_trace_setstatus 2 ,2