MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































Latest SQL Server Tips

Different Ways to Find Default Trace Location in SQL Server

Read Comments   |   Related Topics: SQL Server Indexing Articles & Tips > Next Topic: Database Backup & Restore Tips


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?

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
sys.configurations How to check whether Default Trace is running on SQL Server Instance

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?

Default Trace in SQL Server will capture events and sub events in 6 categories namely:

    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

Become a Database Expert: Update your Database Backup and Restore Skills. For more information, see SQL Server Database Backup Tutorial with Examples.

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.traces Catalog View

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

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 sys.fn_trace_getinfo Function

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 Registry

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
Identify the Default Trace File Location in SQL Server Using SERVERPROPERTY Function

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.

Learn More...







Last Updated On: June 18, 2013



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: