Auditing: SQL Server’s Built-in Traces
Aside from the user-defined traces we have discussed throughout this chapter, SQL Server uses tracing to help you audit user activity using a few built-in configurations. In this section, we will briefly discuss the default trace, blackbox traces, and Common Criteria traces—all part of the same SQL Trace infrastructure, but configured and used slightly differently than the traces discussed in the rest of the chapter.
Default Trace
Upon first being installed, SQL Server 2005 starts up a background trace known as the default trace. If left in place, this trace will have a trace ID of 1, and you can see it in the sys.traces view using the following query:
SELECT *
FROM sys.traces
WHERE id = 1
The trace is fairly lightweight, and includes events for monitoring server starts and stops, object creation and deletion, log and data file auto growth, and other database changes. The information collected by the trace can be retrieved using fn_trace_gettable, just like any other trace. In addition, data from the default trace is used for various reports that ship with SQL Server Management Studio. The trace is also useful as a general log in case something unexpected happens (“hey, who dropped my table!?”), and for all of these reasons we recommend leaving it in place.
Should you feel the need to disable the default trace, you can do so by changing the default trace enabled server option, using the following Transact-SQL:
EXEC sp_configure 'default trace enabled', 0
RECONFIGURE
Note that this will immediately stop the trace; there is no need to restart the SQL Server service.
Blackbox Traces
Another background trace that SQL Server offers right out of the box is the so-called blackbox trace. This trace is designed to behave similarly to an airplane black box, to help you diagnose intermittent server crashes. It is quite a bit heavier than the default trace, and includes the SP:Starting, SQL:BatchStarting, Exception, and Attention events, with several columns from each event included in the trace.
This trace is configured by setting the @options parameter of sp_trace_create to a value of 8. The following Transact-SQL can be used to start a blackbox trace:
DECLARE @TraceId INT
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8
EXEC sp_trace_setstatus @TraceId, 1
The trace is automatically configured to use two rollover files, and flip back and forth between them when reaching its default maximum file size of 5 MB. However, some customers have reported that 5 MB was not enough back data to help them debug crash problems. To expand the size, you’ll have to specify the @maxfilesize parameter, in addition to passing in a value for the @tracefile parameter (even a NULL). The following Transact-SQL code creates a blackbox trace with a 25 MB maximum size:
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL,
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
By default, the path to the blackbox trace file is in the default SQL Server data file folder. However, just like the file size, the path can also be overridden if necessary, by using the @tracefile parameter.
In order to fully take advantage of the blackbox trace if you are facing intermittent problems, you want to make sure that it is always running when your server is running—including after either a planned or unplanned restart. To accomplish this, you can set the blackbox trace to start automatically when SQL Server starts. First, wrap the trace definition in a stored procedure in the master database:
USE master
GO
CREATE PROCEDURE StartBlackBoxTrace
AS
BEGIN
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL,
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
END
GO
Next, set the procedure to start automatically when the SQL Server service is started:
EXEC sp_procoption
'StartBlackBoxTrace', 'STARTUP', 'ON'
In the event of a crash, you can analyze the collected data to determine what stored procedures or queries were running at the point of failure and—hopefully—figure out what went wrong.
C2 and Common Criteria Auditing
SQL Server 2000 added a feature called C2 Audit, which allowed DBAs to meet U.S. government standards for auditing both unauthorized use of and damage to resources and data. A newer, international standard, called Common Criteria, is available in SQL Server 2005 (along with C2, which is still available).
Both of these audit modes utilize SQL Trace to capture a variety of data, including any logins, logouts, access to tables, and many other data points. Both also make use of the value of 4 passed to the @options parameter of sp_trace_create, which automatically shuts down the SQL Server service should any error occur writing to the trace file. This is intended to ensure that even in the case of disk error, none of the audit data will possibly be lost (with the server shut down, no audit data will be generated to lose).
Both C2 and Common Criteria audit modes are enabled or disabled using sp_configure. To enable C2 auditing, configure the C2 Audit Mode option to a value of 1. For Common Criteria, configure the Common Criteria Compliance Enabled option to a value of 1. In both cases, a service restart is required for the auditing to actually start.
© Microsoft. All Rights Reserved.