Audit TRACE restart

[djj55]

Hello, SQL 2008 R2
I have created a trace setup to run from 12:01 AM to 11:59 PM. (Started by Job, stopped by parameter)

Now I noticed that after a restart of the SQL service the trace did not restart.

In fact when I tested, the trace is no longer there when the service is restarted.

Also since the stored procedure which starts the trace has a trace file name (changes by day) rerunning the stored procedure produces an error.
[tt]Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.[/tt]

I see that the default trace (id = 1) restarts and continues on.

How can I get my trace to act like this? i.e. restart after service restart and use next file to save the logs.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[djj55]

Found a how to that got me where I wanted to be. The sp_procoption was needed but the sp_configure was not.

By adding a stored procedure to the master database and running

EXECUTE sp_procoption 'usp_mysp','startup',true

I was able to have it start up after the service restarted.

Note that I had to change the original stored procedure to create an unique filename. To do this I added a letter based on hour of day to the filename which is not the only way to create a unique name.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!