9

When I query the configuration of the default trace, it shows enabled:

exec sp_configure 'default trace enabled';
-->
name                    minimum  maximum  config_value  run_value
default trace enabled         0        1             1          1

But when I query sys.traces for the path, it returns an empty rowset:

select * from sys.traces;

What could explain the absence of the enabled trace?

  • @AaronBertrand: select * from sys.traces returns an empty rowset – Andomar Jul 2 '13 at 19:33
  • Also I have heard of cases where the trace dies because the drive it was writing to filled up (and that won't necessarily affect SQL Server unless in a noticeable / correlatable way). – Aaron Bertrand Jul 2 '13 at 19:38
  • @AaronBertrand: The admin says the drive did fill up a few days ago. Would a restart of the SQL Server service also restart the default trace? – Andomar Jul 2 '13 at 19:40
  • The trace being stopped was my first though too but EXEC sp_trace_setstatus @traceid = 1, @status = 0 brings back The default trace cannot be stopped or modified. so not sure it can be stopped unless there is an error that prevents it from running. Anything in the error logs? – Martin Smith Jul 2 '13 at 19:41
  • @Martin that's right, you can't stop the default trace manually. – Aaron Bertrand Jul 2 '13 at 19:41
13

I would say there is a strong correlation between your out of space event and the missing trace. Note that the sp_configure option merely tells you that the default trace is enabled, but that does not mean that it is running or that it even exists. Note that sys.traces is not a table but a view:

create view sys.traces as select * from OpenRowset(TABLE SYSTRACES)

What does the TABLE SYSTRACES rowset provide? How does it work? How are its results filtered? Your guess is as good as mine. It is possible that the trace is still there, but in a state that prevents it from being exposed by this view. And it may be in a state that still prevents it from being started even after restarting the service.

First, make sure the location of the default trace has sufficient space, the SQL Server service account still has adequate permissions to write to it, you aren't subject to any space quotas, etc. You can get the location from the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\...YourInstance...\Setup\SQLDataRoot\

Once you are sure that SQL Server should be able to write to this folder, then you can disable and re-enable the default trace:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;

You shouldn't need to restart the SQL Server service at this point, but may be a final kick in SQL Server's pants if you still don't see a row in sys.traces. Note that the trace_id you get is not guaranteed to stay at 1.

  • Thanks, disable and re-enable worked. I didn't use with override. The error logs did indeed show an out-of-space event. – Andomar Jul 2 '13 at 20:00
  • 1
    @Andomar sorry, with override is habit. – Aaron Bertrand Jul 2 '13 at 20:08
1

I had the same issue after the drive filled up. The default trace was enabled but not running. Disabling it and re-enabling it worked immediately without stopping services.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.