I was asked to trace department’s access to a certain sql server here.
To create a trace that is run by sql server, open profiler and define your trace. Then, while it is not running, you can script it by going to the File-Script Trace menu and save the sql.
Either make a stored procedure which contains the sql or just paste in into a job step. Have your job run when sql server agent starts or set your procedure as a startup procedure with sp_procoption.
Once the trace is running you can check on it with:
SELECT * FROM ::fn_trace_getinfo ([the trace id])
Stop it with:
exec msdb..sp_trace_setstatus [the trace id] , 0 — stop the trace
exec msdb..sp_trace_setstatus [the trace id] , 2 — close and delete the trace defination
I also added the following so if there was an existing output file it would be renamed.
exec master..xp_cmdshell ‘ren [path to your trace output] “%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%-[some common name]”‘
Also, I increased sp_trace_create’s parameters @options to rollover to a new file and @maxfilesize to a much larger file size just in case.
Later I will import the results into sql server and total the cpu, duration, and reads and writes by workstation.