SQL Server Default Trace

Microsoft SQL Server

The default setting in SQL Server 2005 and later is to keep a trace running all the time that captures basic information about the instance. This is called the Default Trace. The created trace files can be viewed in Performance Monitor or by using T-SQL and they can be very useful in some troubleshooting scenarios.

SQL Server keeps the trace history in five log files. Each log file can be up to 20 Mb. So there is no way of knowing how long each trace event will stay in the log files before deletion. It depends of how many events the instance is generating.

To check if the default trace is enabled, use:

SELECT value_in_use FROM sys.configurations WHERE NAME = 'default trace enabled'

To enable or disable the default trace, use:

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

The trace is light weight, so I recommend keeping it running unless you suspect you’d get some performance benefits from disabling it.

To see where SQL Server keeps the trace files, use:

SELECT [path] FROM sys.traces WHERE is_default = 1

That statement will show the current trace file that SQL Server is logging to.

View the default trace in SQL Server Profiler

The trace files can be opened and viewed like any other trace file with SQL Server Profiler. Profiler can also be used to see the events that are collected. Select File -> Properties and the Events Selection tab:

Default Trace File Properties

Default Trace File Properties

The following list shows the events that are captured by the default trace. The descriptions are from MSDN. There are traces in the following categories: Database, Errors and Warnings, Objects, Performance, Security Audit, Server and Full text. The list is based on a SQL Server 2014 instance:

Database

92 – Data File Auto Grow

Indicates that a data file was extended automatically by the server.

93 – Log File Auto Grow

Indicates that a log file was extended automatically by the server.

94 – Data File Auto Shrink

Indicates that a data file was shrunk automatically by the server.

95 – Log File Auto Shrink

Indicates that a log file was shrunk automatically by the server.

167 – Database Mirroring State Change

Occurs when the state of a mirrored database changes.

Errors and Warnings

22 – ErrorLog

Indicates that error events have been logged in the SQL Server error log.

55 – Hash Warning

Indicates that a hashing operation (for example, hash join, hash aggregate, hash union, and hash distinct) that is not processing on a buffer partition has reverted to an alternate plan. This can occur because of recursion depth, data skew, trace flags, or bit counting.

69 – Sort Warnings

Indicates sort operations that do not fit into memory. Does not include sort operations involving the creating of indexes; only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

79 – Missing Column Statistics

Column statistics that could have been useful for the optimizer are not available.

80 – Missing Join Predicate

Query that has no join predicate is being executed. This could result in a long-running query.

Objects

46 – Object:Created

Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.

47 – Object:Deleted

Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

164 – Object:Altered

Occurs when a database object is altered.

Performance

218 – Plan Guide Unsuccessful

Indicates that SQL Server could not produce an execution plan for a query or batch that contained a plan guide. SQL Server attempted to generate an execution plan for this query or batch without applying the plan guide. An invalid plan guide may be the cause of this problem. You can validate the plan guide by using the sys.fn_validate_plan_guide system function.

Security Audit

18 – Audit Server Starts and Stops

Occurs when the SQL Server service state is modified.

20 – Audit Login Failed

Indicates that a login attempt to SQL Server from a client failed.

102 – Audit Database Scope GDR

Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server for database-only actions such as granting permissions on a database.

103 – Audit Object GDR Event

Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.

104 – Audit AddLogin Event

Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin.

105 – Audit Login GDR Event

Occurs when a Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.

106 – Audit Login Change Property Event

Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage.

108 – Audit Add Login to Server Role Event

Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember.

109 – Audit Add DB User Event

Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser.

110 – Audit Add Member to DB Role Event

Occurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup.

111 – Audit Add Role Event

Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole.

115 – Audit Backup/Restore Event

Occurs when a BACKUP or RESTORE command is issued.

116 – Audit DBCC Event

Occurs when DBCC commands are issued.

117 – Audit Change Audit Event

Occurs when audit trace modifications are made.

152 – Audit Change Database Owner

Occurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that.

153 – Audit Schema Object Take Ownership Event

Occurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that.

175 – Audit Server Alter Trace Event

Occurs when a statement checks for the ALTER TRACE permission.

Server

81 – Server Memory Change

SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.

Full text

155 – FT:Crawl Started

Occurs when a full-text crawl (population) starts. Use to check if a crawl request is picked up by worker tasks.

156 – FT:Crawl Stopped

Occurs when a full-text crawl (population) stops. Stops occur when a crawl completes successfully or when a fatal error occurs.

To get the list above with T-SQL, use:

SELECT
	TC.name AS Category
	,TE.trace_event_id as EventId
	,TE.name as [Event]
FROM
	sys.trace_events AS TE
JOIN
	sys.trace_categories AS TC
ON
	TE.category_id = TC.category_id
WHERE
	TE.trace_event_id IN (18,20,22,46,47,55,69,79,80,81,92,93,94,95,102,103,104,105,106,108,109,110,111,115,116,117,152,153,155,156,164,167,175,218)
ORDER BY
	TC.category_id
	,TE.trace_event_id

View the default trace with T-SQL

The trace files can also be queried with T-SQL, using the system table sys.fn_trace_gettable. This detailed blog post shows how to query all the log trace files at once.

SELECT TOP 100
	TC.name AS TraceCategoryName
	,TE.name as TraceEventName
	,TCV.subclass_name
	,TRC.*
FROM
	::fn_trace_gettable((SELECT LEFT([path], LEN([path]) - PATINDEX('%\%', REVERSE([path]))) + '\log.trc' FROM sys.traces WHERE is_default = 1), DEFAULT) AS TRC
JOIN
	sys.trace_events AS TE
ON
	TRC.EventClass = TE.trace_event_id
JOIN
	sys.trace_categories AS TC
ON
	TE.category_id = TC.category_id
LEFT OUTER JOIN
	sys.trace_subclass_values AS TCV
ON
	TE.trace_event_id = TCV.trace_event_id
AND
	TRC.EventSubClass = TCV.subclass_value

The content varies depending on the type of event, but some general columns are:

Event Class

This is the event identifier, as in the list above. For instance, 69 for a sort warning. The complete list of events can be found in the system table sys.trace_events.

Trace Category

Events can be further grouped into trace categories that can be found in sys.trace_categories. This is the same as the headers in the list above. For instance, a 69 – Sort Warning belongs to the category Errors and Warnings.

Event Sub Class

Events can be further divided into sub classes (for instance drop or create) that can be found in the system table sys.trace_subclass_values.

Object Type

The column ObjectType refers to a list of object types. They are not available in SQL Server (that I know of), but the list can be found at MSDN.

Leave a Reply

Required fields are marked *.