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?
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.
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.
