SQL Server Default Tracing--Introduction

SQL Server Default Tracking--Introduction What is the default trace?

The default SQL Server predefined trace, which is the most lightweight trace that is turned on by default in SQL Server, consists of 5 trace files (. trc), with a maximum value of 20MB per file, stored in the SQL Server log directory.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/53/C4/wKiom1RvE8LihOLlAAC-uN5J-PY256.jpg "height=" 171 "border=" 0 "/>

These files are used as caches of temporary storage capture events. Events that are stored in the cache are deleted after an event. When SQL Server restarts, or when the file currently in use reaches the maximum, the oldest file is deleted, and in a busy production environment, such a cyclic cache is overwritten within a few minutes.

Attention:

Subsequent versions of Microsoft SQL Server will remove the feature. Avoid using this feature in new development work, and start modifying applications that are currently using the feature. Use extended events instead.

What is the role of default tracking?

How do I know who deleted the table? How do I track the autogrow event? How do I know when memory usage changes? Does the security settings change?

The default trace ensures that the database administrator provides troubleshooting help for database administrators when the problem first occurs with the log data that is required to diagnose the problem.

How do I know if the default trace is running?

SELECT * from sys.configurations WHERE configuration_id = 1568

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M01/53/C4/wKiom1RvE8OgIGL4AABJcidKeac521.jpg "height=" Wuyi "border=" 0 "/>

How do I turn on default tracking?

Use the default trace enabled server configuration option to enable or disable the defaults trace log file. The default tracing feature provides a rich, persistent activity log and is primarily changed based on configuration options.

sp_configure ' show advanced options ', 1; Goreconfigure; Gosp_configure ' default trace enabled ', 1; Goreconfigure; GO

If you set the default trace enabled option to 1, you can turn on the defaults trace. The default setting for this option is 1 (on). A value of 0 o'clock turns off tracing.

The default trace enabled option is an advanced option. If you use the sp_configure system stored procedure to change this setting, you can change the default trace enabled option only if show advanced options is set to 1 o'clock. This setting takes effect immediately, without restarting the server.

can be done by

EXEC sp_configure ' default trace enabled ', 0; RECONFIGURE with OVERRIDE;

To turn off default tracing.

How do I view the default trace file?

SELECT * FROM:: fn_trace_getinfo (default)

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M02/53/C4/wKiom1RvE8OR2Hu2AAClMFcncc4172.jpg "height=" 127 "border=" 0 "/>

Explain the tracking properties:

Column Name	Data type	Description
Traceid	Int	ID of the trace.
Property	Int	property of the trace: 1= Trace Options. For more information, see @options in sp_trace_create (Transact-SQL). 2 = File Name 3 = Max Size 4 = Stop Time 5 = Current trace status
Value	sql_variant	Information about the property of the trace specified.

In a typical installation of SQL Server, the default trace is enabled and thus becomes Traceid 1. If enabled after installing and creating additional traces, the traceid can become a larger number.

SELECT * from Sys.traces;

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M00/53/C2/wKioL1RvFD6ixXwTAAA_DqIGeWs079.jpg "height=" "border=" 0 "/>

what does the default trace file record?

The default trace log can be opened and checked through SQL Server Profiler, or queried using fn_trace_gettable system functions through Transact-SQL.

SQL Server Profiler can open the default trace log file just as you would open a normal trace output file. By default, the default trace log is stored in the \mssql\log directory as a rolling update trace file. The base file name for the default trace log file is log.trc.

When we open the default trace file with Profiler, we can see that there are 7 types of captured events from the properties:

? Database

? Errors and Warnings

? Full-text

? Objects

? Performance

? Security Audit

? Server

Also, all optional columns for each subclass event are selected.

650) this.width=650; "title=" clip_image009 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M01/53/C2/wKioL1RvFD_C9zPfAAEWgsBPlYA778.jpg "height=" 333 "border=" 0 "/>

The following statement opens the default trace log in the default location:

SELECT *from fn_trace_gettable (' D:\SQL-DATA\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_1019.trc ', default); GO

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1581007

SQL Server Default Tracing--Introduction