Different Ways to Find Default Trace Location in SQL Server

How to Find SQL Server Default Trace Location

Starting SQL Server 2005, Microsoft introduced a light weight trace which is always running by default on every SQL Server Instance. The trace will give very valuable information to a DBA to understand what is happening on the SQL Server Instance. In this tip we will take a look at different ways to find the location of default trace file in SQL Server and how to enable or disable default trace file in SQL Server.

Firstly, let us start by answering very basic questions such as:

What is the Default Trace in SQL Server?

The trace will give very valuable information to a DBA to understand what is happening on the SQL Server Instance. 

Where can i find the Default Trace file in SQL Server?

One can easily check whether the Default Trace is running on SQL Server Instance by executing the below TSQL query on the instance of SQL Server.

SELECT * FROM sys.configurations 
WHERE configuration_id = 1568
GO

How to Enable Default Trace in SQL Server if it is not Enabled Already?

One can easily enable Default Trace in SQL Server if it is not already enabled by executing the below TSQL query on the instance of SQL Server.

/* Show Advanced Option */

SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE 
GO

/* Enable Default Trace in SQL Server */

SP_CONFIGURE 'default trace enabled',1
GO
RECONFIGURE 
GO

How to Disable Default Trace in SQL Server if it Enabled Already?

One can easily disable Default Trace in SQL Server if it is enabled by executing the below TSQL query on the instance of SQL Server.

/* Disable Default Trace in SQL Server */

SP_CONFIGURE 'default trace enabled',0
GO
RECONFIGURE 
GO

/* Hide Advanced Option */

SP_CONFIGURE 'show advanced options', 0
GO
RECONFIGURE 
GO

What is Captured in Default Trace in SQL Server?

Database Events

• Data file auto grow
• Data file auto shrink
• Database mirroring status change
• Log file auto grow
• Log file auto shrink

Errors and Warnings

• Errorlog
• Hash warning
• Missing Column Statistics
• Missing Join Predicate
• Sort Warning

Full-Text Events

• FT Crawl Aborted
• FT Crawl Started
• FT Crawl Stopped

Objects Events

• Object Altered
• Object Created
• Object Deleted

Security Audit Events

• Audit Add DB user event
• Audit Add login to server role event
• Audit Add Member to DB role event
• Audit Add Role event
• Audit Add login event
• Audit Backup/Restore event
• Audit Change Database owner
• Audit DBCC event
• Audit Database Scope GDR event (Grant, Deny, Revoke)
• Audit Login Change Property event
• Audit Login Failed
• Audit Login GDR event
• Audit Schema Object GDR event
• Audit Schema Object Take Ownership
• Audit Server Starts and Stops

Server Memory Change Events

• Server Memory Change Events

Different Ways to Find Default Trace Location in SQL Server

Let us know explore different ways in which you can identify the location of default trace file in SQL Server.

• Find Default Trace File Location Using sys.traces Catalog View
• Find Default Trace File Location Using sys.fn_trace_getinfo Function
• Find Default Trace File Location Using Registry
• Find Default Trace File Location Using SERVERPROPERTY Function

Identify the Default Trace File Location in SQL Server Using sys.traces Catalog View

SELECT path AS [Default Trace File]
	,max_size AS [Max File Size of Trace File]
	,max_files AS [Max No of Trace Files]
	,start_time AS [Start Time]
	,last_event_time AS [Last Event Time]
FROM sys.traces
WHERE is_default = 1
GO

Identify the Default Trace File Location in SQL Server Using sys.fn_trace_getinfo Function

SELECT value AS [Deafult Trace File]
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2
GO

Identify the Default Trace File Location in SQL Server Using Registry

DECLARE @DefaultTraceFileLocation NVARCHAR(500)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\Setup'
,N'SQLDataRoot'
,@DefaultTraceFileLocation OUTPUT
,'no_output'

SELECT @DefaultTraceFileLocation + N'\Log\' AS [Deafult Trace Location]
GO

Identify the Default Trace File Location in SQL Server Using SERVERPROPERTY Function

SELECT REPLACE(CONVERT(VARCHAR(500), SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG', '\') 
AS [Deafult Trace Location]
GO

Conclusion

In this article we have explored many options to identify the default trace file location in SQL Server. However, the easiest way is to use SERVERPROPERTY function to find the default trace location.