http://www.sqlservercentral.com/blogs/jeffrey_yao/2010/08/13/restart-sql-server-default-trace/

Printed 2014/04/18 07:24AM

Restart SQL Server Default Trace

By Jeffrey Yao, 2010/08/13

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!

 

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.