How to Enable and Disable the Default Trace in SQL Server

Default Trace in SQL Server can be enabled or disabled using the sp_configure system stored procedure.
Set the 'default trace enabled' advanced option to 1 (which is default setting for this option) to enable the default trace or set it to 0 to disable the default trace.
To change the settings for any of the advanced options (like 'default trace enabled' option) using the sp_configure system stored procedure, 'show advanced options' must be set to 1.

To enable the default trace:

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

To disable the default trace:

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

To check whether the default trace is ON (1), or OFF (0):

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

You can get additional information for all traces in the instance of SQL Server:

SELECT * FROM :: fn_trace_getinfo(default)

This will give you a list of all of the traces that are running on the server.

The property of the trace as represented by the following integers:

1 – Trace Options (@options in sp_trace_create)
2 – FileName
3 – MaxSize
4 – StopTime
5 – Current Trace status (1 = On and 0 = Off)

fn_trace_getinfo_all_traces

If the post helped you, please share it:
Pin It

2 comments to How to Enable and Disable the Default Trace in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">