This site is currently using a generated translation

SQL Server - default race

Who removed the table? When did the user get rights? Many people don't know about it but SQL Server can tell you a lot. Since SQL Server 2005, there is a default trace set on all instances that contains a lot of useful information when troubleshooting.

Normally, the default trace is enabled and automatically started at instance startup. It takes very little resources to have enabled and normally consists of five rolling trace files with the base name logNN.trc (where NN is a sequence number) stored under MSSQLLOG under the installation path of the instance and usually has traceid 1. Both path and number of files can be set and it is usually recommended to increase the number of log files depending on the activity on the server and the monitoring time requirements.

Activation and set-up
Run the query below to check if the feature is active on your instance:

SELECT value_in_use FROM master.sys.configurations WHERE configuration_id = 1568
If it is not active and you want to start it, you can run this script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

To see more information about it, e.g. path to the trace files, trace id etc., there is the function fn_trace_getinfo which, if run with default value, shows information about all active tracers on the instance:
 SELECT * FROM ::fn_trace_getinfo(DEFAULT)

The trace files can be opened as normal .trc files in SQL Server Profiler or read via T-SQL with the function fn_trace_gettable.

Events
Via fn_trace_geteventinfo or SQL Server Profiler tracedefinition you can see all events traced on your instance. What we mostly use it for is to retrieve information about auto growth for database files, change of database objects and security events.
Auto growth is represented by four different events that occur when expanding and contracting the data and log files respectively for all databases.

92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink

Security-related events are most often used to investigate security issues but also to track rights changes etc. The default trace includes:

20 Audit Login Failed
102 Audit Database Scope GDR event (Grant, Deny, Revoke)
104 Audit Add login event
105 Audit Login GDR event (Grant, Deny, Revoke)
108 Audit Add login to server role event
109 Audit Add DB user event
110 Audit Add Member to DB role event

The events available for object changes are used to track changes in databases, e.g. who/what deleted the table, etc.

46 Object:Created
47 Object:Deleted
164 Object:Altered

For these events, you just need to know the ObjectType column which tells you what kind of object it is. For example:

8272 Stored procedure
8277 Table
16964 Database

Example
Below SQL statement retrieves info from the active default trace file, in this case about failed logins. To determine which events to display, just replace EventClass with e.g. one of the events I specified above. You can also specify a filename directly or specify how many files the function should fetch data from. There are also many more columns in the trace file that may be needed depending on which events you are interested in. Please have a look at fn_trace_gettable in the SQL Server documentation.

 SELECT e.name AS [EventName]
,t.DatabaseName AS [Database]
,t.ApplicationName AS [Client application]
,t.LoginName AS [LoginName]
,t.StartTime AS [StartTime]
,t.EndTime AS [EndTime]
,t.ObjectName AS [ObjectName]
,t.ObjectType AS [ObjectType]
FROM [sys].[fn_trace_gettable](CONVERT(VARCHAR(150), (
SELECT TOP 1 tf.[value]
FROM [sys].[fn_trace_getinfo](NULL) AS tf
WHERE tf.property = 2
)), 1) AS t
INNER JOIN [sys].[trace_events] e ON t.EventClass = e.trace_event_id
WHERE t.EventClass IN (20)
ORDER BY t.StartTime ;

A little side note at the end, Microsoft has indicated that the defaults trace will be replaced by an extended events in future versions of SQL Server.
/Björn