Monday, December 27, 2010

How to Stop and Start Default Trace In SQL Server

How can I find out if the trace is running on my SQL Server?
 
The simplest way to determine, if the trace is running or not in the server, is to execute the following commands.
 
SELECT *
FROM fn_trace_getinfo(default);
GO 

What does the output indicate?

Result Set
Description
Traceid
Unique identifier for the trace
Property = 1
Configured trace options
Property = 2
Trace file name
Property = 3
Max file size for the *.trc file
Property = 4
Stop time for the trace session
Property = 5
Current trace status (1 = On and 0 = Off)
Value
Current value for the traceid\property combination

Where is this trace file stored by default?
The trace is stored in the LOG directory for your SQL Server instance (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) at the root of where your SQL Server is installed.

Another alternative to determine if the trace is running is to review sp_configure.
To determine if the trace is configured to run, execute sp_configure and review the 'default trace enabled' option. When the config_value and run_value are equal to 1, then this trace is running.

How can I disable this default trace?

To disable the default trace from running, issue the following commands:

EXEC master.dbo.sp_configure 'allow updates', 1;
GO
EXEC master.dbo.sp_configure 'show advanced options', 1;
GO
EXEC master.dbo.sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'allow updates', 0; 
GO

When you execute above commands, then the default trace stops executing immediately.
default trace enabled Server Configuration Option. Default trace. Whats in the default trace. Default Trace in SQL Server 2005. The default trace in SQL Server. Default Trace in SQL Server 2005. An Introduction to the SQL Server Default Trace. Default Trace and System Health. how to stop default trace. how to start default trace. in sql server 2012. how to stop default trace in sql server 2008. sql server 2005. Audit SQL Server with the Default Trace. Troubleshooting SQL Server using the in-built default trace. default trace in sql server 2008. default trace in sql server 2012. Finding Trace Files. finding trace file location. trace file location. Restart SQL Server Default Trace. Using the Default Trace in SQL Server 2005 and SQL Server 2008.

1 comments:

Post a Comment