 |
|
SQL Product Highlight
Red Gate Software - SQL Monitor
SQL Server performance monitoring and alerting - SQL Monitor offers an easy entrance to advanced server monitoring with a simple design that's a refreshing change from the status quo. Red Gate have added custom metrics and user roles to the product without spoiling its ease-of-use, to help you answer that timeless question, 'How healthy are your servers?'Learn more!
Default Trace in SQL Server 2005
|
By: Jeremy Kadlec | Read Comments (5) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More |
|
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
- Check out your SQL Servers to determine if they are running the default trace. Chances are that the trace is running and you did not even know it.
- Review the default trace parameters to determine the directory where the trace is writing the output files.
- Next ensure you have sufficient storage to support the files.
- To see the value in the data, review the output from the trace files to determine if any of the entries will serve as answers to outstanding questions.
- Check out these MSSQLTips on Profiler and auditing:
Last Update: 11/15/2006
| Share: |  |
Share | Tweet |
|
 |
 |
Connect with MSSQLTips.com |
Comments and Feedback:
| Thursday, June 05, 2008 - 11:15:48 AM - trooper665 | Read The Tip |
|
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? |
|
| Friday, August 21, 2009 - 4:01:24 AM - nparikh | Read The Tip |
|
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
|
|
| Tuesday, March 15, 2011 - 9:36:41 AM - Bob | Read The Tip |
|
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/ |
|
| Tuesday, May 10, 2011 - 12:53:30 AM - Feodor | Read The Tip |
|
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 |
|
| Wednesday, January 04, 2012 - 7:36:23 AM - Krzysiek | Read The Tip |
|
Thank you very much for this advice. It very accurate and conscise as it shoud be. It is excelent :) |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |