How to create a Profiler Trace during Server startup

Posted on by

I think I answered this question more than once on multiple different forums. So, I thought it was time to create a blog post on it.   

One of my previous post has the steps to create a .sql file containing the server side trace definition. Once you have the .sql file generated by the SQL Profiler Trace GUI. Use the script to convert that into the stored procedure with the following changes:   

declare @tracefilename nvarchar(255) 

set @tracefilename = N'D:\StartupTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL

You will have to add the lines highlighted above and the make a change for the trace file name in the line that is not highlighted. This will ensure that every time your SQL instance starts up, you have a unique trace file name. If the filename is not unique, then the second time the service restarts you will get a trace creation error stating that the file already exists. You will see the following error in your SQL Errorlog:   

Launched startup procedure ‘usp_startup_trace’.

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).

Error: 19062, Severity: 16, State: 1.Could not create a trace file. 

After this is done, create the stored procedure to create the trace definition. Let’s say the proc name was “usp_startup_trace”.   

Now, you will need to mark this stored procedure as a startup procedure using the following:

EXEC sp_procoption 'usp_startup_trace','startup',true

 Once that is done, you will need to set the configuration options for the instance to scan for startup procedures. The default is set to 0.   

EXEC sp_configure 'scan for startup procs',1 RECONFIGURE

 When your SQL instance restarts the next time a new file will be created for the profiler trace and sys.traces will show you new trace active on the server.  

Reference: sp_procoption