To create a trace, you execute the stored procedure sp_trace_create. This stored procedure takes several arguments, including an OUTPUT variable that records the trace identifier, options for the trace, and the name of the file to write the trace to. Web Listing 1 (http://www.secadministrator.com, InstantDoc ID 25728) shows a SQL Server script that creates a trace. Simply type the commands into a file called createtrace.sql, log in to the system with an account that has DBA privileges, and type
osql —E —S
<servername> —i createtrace.sql
Createtrace.sql creates the log file C:\sqlauditfile.trc. (The database server appends the suffix automatically, even if you specify a .trc filename extension.) The value 6, which appears on createtrace.sql's second line, represents the bit mask that defines the trace's options. Createtrace.sql sets the bits TRACE_FILE_ROLLOVER (2) and SHUTDOWN_ON_ERROR (4). When the trace file reaches its maximum size, SQL Server stops it and appends an integer value to its filename. For example, when sqlauditfile.trc becomes full, SQL Server names it sqlauditfile.trc_1 and creates a new sqlauditfile.trc file. If a file called sqlauditfile.trc_1 already exists, SQL Server uses sqlauditfile.trc_2 for the old file, and so on. The default maximum size for a log file is 5MB, but you can change this default value by specifying an additional argument to the stored procedure. Createtrace.sql's PRINT statement displays the trace identifier issued to the newly created trace. This identifier is required to configure and start the trace. . . .
Register
