Default Trace in SQL Server 2005

By:   |   Comments (11)   |   Related: > Profiler and Trace


Problem

I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues.  I have noticed an unusual trace session that is running.  I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance.  So where did this trace session coming from?

Solution

SQL Server 2005 is running a default trace.  You can think of this as a replacement to the black box mode trace that could have been run in SQL Server 2000 where the last 5 MB of data is captured.  The trace's impact should be minimal to the server, but is valuable to be aware of as a DBA or Developer responsible for the server.

How can I find out if the trace is running on my SQL Server?

The simplest means to determine if the trace is running is to execute the following command:

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

*** NOTE *** - When you issue these commands, the trace stops executing immediately.

Should I disable this trace?

If you were not aware of this trace running and you were not having related or suspected performance issues from this trace, I would say maybe not.  The final answer should come after reviewing the output from the trace files to determine if the data is valuable to you.  Invaluable information like login creations and drops are captured in these files.  You might also find other jewels that may answer some recent outstanding questions.  If the value of the information exceeds the potential issue from running this trace, then it should remain enabled.  I also encourage you to consider this trace as a source when troubleshooting an issue, so consider reviewing this file the next time a question goes unanswered.

How can I review the data captured in the trace files?

Let's end on an easy question.  Just navigate to the directory where the files are located i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ and double click on the files.  Profiler should load and permit you to browse the contents interactively.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, September 5, 2019 - 7:32:04 AM - Anubhab Back To Top (82249)

Hello, I am getting same error one of the SQL 2016 server. So i went to reports >> standerd repotrs >> schma changes history and getting below error.

Schema Changes History

on JIVVDSQL02\SQL2016 at 9/5/2019 4:33:39 AM

This report provides a history of all committed DDL statement executions recorded by the default trace.

#Error

Shows changes made in the schema of the objects by DDL operations.

Msg 19050, Level 16, State 1
Unable to retrieve data for this section of the report. Following error occurred.
Trace file name '' is invalid.

I have tried to execute below command and i am getting some output.

sp_configure

execute sp_configure and review the 'default trace enabled' option. When the config_value and run_value are equal to 1. but my reports is not ganareting.

Could please help.

Friday, July 18, 2014 - 7:00:12 PM - Kelvin Rance Back To Top (32783)

Running  SQL Server 2014. Default trace running as confirmed by SELECT * FROM fn_trace_getinfo(default). As per this article, went to the log directory where the default  .trc files are located, then double clicked on the default .trc file. Profiler gave a message stating that could not open default .trc file due to permission problem..... I am the sole user, owner of the pc, server etc. However, I could select into a temporary table the default .trc file. Anyone with any comments????????

Thursday, July 5, 2012 - 9:36:15 PM - Nizar Back To Top (18365)

Thanks Jeremy. It is very helpful.

Monday, June 11, 2012 - 9:54:22 AM - satish neelakandan Back To Top (17929)

Greg Robidoux, Thank you so much.

Satish 

Thursday, June 7, 2012 - 5:17:27 PM - Greg Robidoux Back To Top (17860)

@satish - you can try to use something like one of these tips

http://www.mssqltips.com/sqlservertip/1374/clr-function-to-delete-older-backup-and-log-files-in-sql-server/

http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups/

or just use xp_cmdshell to delete the file

Thursday, June 7, 2012 - 5:10:45 PM - satish neelakandan Back To Top (17858)

I'm doing a performance testing, where I have created a job which captures the SQL statements each and every day. The job will save the details into trace .trc file automatically in one of the servers. I have created another job which runs at 6 in the evening, which will copy the information from the trace file then insert in to our necessary table using ::fn_trace_gettable.

My question is how to delete this .trc file using a SQL query. I mean to clearly state that when the job running on the next day it will says this file already exists. so, i need to delete this file automatically using a sql query. I can delete it manually but I need to use SQL

Wednesday, January 4, 2012 - 7:36:23 AM - Krzysiek Back To Top (15520)

Thank you very much for this advice. It very accurate and conscise as it shoud be. It is excelent :)

Tuesday, May 10, 2011 - 12:53:30 AM - Feodor Back To Top (13789)

Here is a nice set of reports for the SSMS which analyze data from the default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq

Tuesday, March 15, 2011 - 9:36:41 AM - Bob Back To Top (13218)

Here is a very detailed article about the default trace: http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

Friday, August 21, 2009 - 4:01:24 AM - nparikh Back To Top (3935)

I have tried the code you have provided for serverside trace but i am facing strange problem - events and column filters provided not being recognised and trace contains all events and columns. Is there any specific configuration needs to be taken care to handle this?

Thanks

 

 

Thursday, June 5, 2008 - 11:15:48 AM - trooper665 Back To Top (1092)

When I check sp_configure the default trace is configured, however when I run SELECT * FROM fn_trace_getinfo(default) nothing is returned.  I just took ownership of this server so any past activity could have happened.  Does anyone know what happened here?















get free sql tips
agree to terms