Stop MSSQL Trace

Default Trace in SQL Server can be enabled or disabled using the sp_configure system stored procedure.

Enabled:

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

Disabled :

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)