sql traces

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.

This entry was posted in DBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *