Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Restart SQL Server Default Trace

One of my test servers, due to the unexpected growth of a user db, the local drive is used up, and because the original default trace was on the same local drive,  the default trace stopped. After cleanup the local drive to have sufficient free space, I want to restart the default trace. Since I do not want to restart the sql service as it may disrupt other QA activities, I googled and tried to see how I can restart the default trace without restarting the sql server service, but I did not get any immediate answer, maybe no one has been in my situation, so I tried by myself.

I first checked sp_configure 'default trace', it is 1 as expected. So I did

exec sp_configure 'defalut trace', 1;

reconfigure with override;

 

then I check the trace

select * from sys.traces

There is no trace. So I did the following

exec sp_configure 'defalut trace', 0;

reconfigure with override;

go

exec sp_configure 'defalut trace', 1;

reconfigure with override;

go

Then I check the trace again. Aha... the default trace starts!

 

 

Comments

Posted by Jason Brimhall on 13 August 2010

Thank you for sharing this experience and lesson.

Posted by Robert L Davis on 13 August 2010

You should be aware the using RECONFIGURE can flush the cache. This was a test server, so probably no biggie, but I wouldn't recommend trying it on a live production server.

There is another way to get it going again. Use sp_trace_setstatus to start it running again. The default trace is always trace ID 1 (first parameter to the procedure) and a status of 1 means to start the trace (second parameter). So you would use the following command to restart it:

Exec sp_trace_setstatus 1, 1

Posted by Jeffrey Yao on 14 August 2010

Robert, you cannot use sp_trace_setstatus 1, 1 to start the default trace, actually not any trace, if the trace is closed (i.e. its definition is out of memory), which is exactly my case.

Otherwise, you will see the error msg

"Msg 19059, Level 16, State 1, Procedure sp_trace_setstatus, Line 1

Could not find the requested trace.

"

For default trace, you cannot even use sp_trace_setstatus to  stop / close it, otherwise you will see the error msg:

"

Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1

The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

"

Posted by forsqlserver on 20 January 2012

Jason R U Sure?

It is the production on my site?

Posted by Dream Weaver on 21 February 2012

On Sql Server 2008, the sys.traces view shows traces that are currently defined, whether they're running or not.  Usually, if the default trace is running, you'll see it here, with status=1.

But if the default trace is stopped due to an error such as out of disk space, it disappears from this view.  The view will show no results if there are no other traces created either.  But if someone creates one at that point, THAT trace shows up with an ID of 1.  So you can't always guarantee that the default trace has an id of 1.

Since the default trace is not defined when it stops due to an error, there's no way that I know of to restart it other than disabling/reenabling it as shown above.

Posted by Jim McLeod on 31 October 2012

I've just encountered this problem, having the default trace close and disappear from sys.traces (and a subsequent trace becoming Trace #1).

Jeffrey's code in this post worked (although the new default trace was #3), and the contents of sys.dm_exec_cached_plans did not change when RECONFIGURE was run.

That said, Robert's warning is still relevant, because a different sp_configure setting may have been changed, just waiting for a RECONFIGURE to run (although it's a rare occurrence that you'd change a setting and not apply it immediately).

Leave a Comment

Please register or log in to leave a comment.