Creating trace with system stored procedures

Following are the stored procedures which you should know:

• sp_trace_create: This stored procedure is used to create a trace and returns the ID of newly created trace
• sp_trace_setevent: This stored procedure is used to add or remove event classes and data columns to and from a given trace
• sp_trace_setfilter: This stored procedure is used to set a filter condition on desired data column for a given trace
• sp_trace_setstatus: This stored procedure is used to start, stop, or close a given trace

In this example, we will capture only two event classes:

• Data File Auto Grow
• Log File Auto Grow

For these mentioned event classes, we will be capturing the following data columns:

• DatabaseName
• FileName
• StartTime
• EndTime

By collecting these data columns, we can know which database file is automatically grown for which database and when.
We will not apply any filter in this trace because we want to capture and audit the database file growth events for all databases on the server. Thus, stored procedure sp_trace_setfilter will not be used in our example.

Follow the steps provided here to create a trace with system stored procedures:

1. Start SQL Server Management Studio and connect to SQL Server.
2. In the query window, type and execute the following T-SQL script to create a new trace through system stored procedures:

DECLARE @ReturnCode INT

DECLARE @TraceID INT

DECLARE @Options INT = 2

DECLARE @TraceFile NVARCHAR(245) = 'C:\MyTraces\MyTestTrace'

DECLARE @MaxFileSize INT = 5

DECLARE @Event_DataFileAutoGrow INT = 92

DECLARE @Event_LogFileAutoGrow INT = 93

DECLARE @DataColumn_DatabaseName INT = 35

DECLARE @DataColumn_FileName INT = 36

DECLARE @DataColumn_StartTime INT = 14

DECLARE @DataColumn_EndTime INT = 15

DECLARE @On BIT = 1

DECLARE @Off BIT = 0

--Create a trace and collect the returned code.

EXECUTE @ReturnCode = sp_trace_create

@traceid = @TraceID OUTPUT

,@options = @Options

,@tracefile = @TraceFile

--Check returned code is zero and no error occurred.

IF @ReturnCode = 0

BEGIN

BEGIN TRY

--Add DatabaseName column to DataFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_DataFileAutoGrow

,@columnid = @DataColumn_DatabaseName

,@on = @On

--Add FileName column to DataFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_DataFileAutoGrow

,@columnid = @DataColumn_FileName

,@on = @On

--Add StartTime column to DataFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_DataFileAutoGrow

,@columnid=@DataColumn_StartTime

,@on = @On

--Add EndTime column to DataFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_DataFileAutoGrow

,@columnid = @DataColumn_EndTime

,@on = @On

--Add DatabaseName column to LogFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_LogFileAutoGrow

,@columnid = @DataColumn_DatabaseName

,@on = @On

--Add FileName column to LogFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_LogFileAutoGrow

,@columnid = @DataColumn_FileName

,@on = @On

--Add StartTime column to LogFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_LogFileAutoGrow

,@columnid=@DataColumn_StartTime

,@on = @On

--Add EndTime column to LogFileAutoGrow event.

EXECUTE sp_trace_setevent

@traceid = @TraceID

,@eventid = @Event_LogFileAutoGrow

,@columnid = @DataColumn_EndTime

,@on = @On

--Start the trace. Status 1 corresponds to START.

EXECUTE sp_trace_setstatus

@traceid = @TraceID

,@status = 1

END TRY

BEGIN CATCH

PRINT 'An error occurred while creating trace.'

END CATCH

END

GO

3. By executing the following query and observing the result set, make sure that the trace has been created successfully. This query should return a record for the trace that we created:

--Verify the trace has been created.

SELECT * FROM sys.traces

GO

4. The previous query will give you the list of traces that are currently running on the system. You should see your newly created trace listed in the result set of the
previous query. If the trace could be created successfully, execute the following T-SQL script to create a sample database and insert one million records:

--Creating Sample Database keeping Filegrowth Size

--to 1 MB for Data and Log file.

CREATE DATABASE [SampeDBForTrace] ON PRIMARY

(

NAME = N'SampeDB'

,FILENAME = N'C:\MyTraces\SampeDBForTrace_Data.mdf'

,SIZE = 2048KB , FILEGROWTH = 1024KB

)

5. After executing the previous script, execute the following T-SQL script to stop and close the trace:

DECLARE @TraceID INT

DECLARE @TraceFile NVARCHAR(245) = 'C:\MyTraces\MyTestTrace.trc'

--Get the TraceID for our trace.

SELECT @TraceID = id FROM sys.traces

WHERE path = @TraceFile

IF @TraceID IS NOT NULL

BEGIN

--Stop the trace. Status 0 corroponds to STOP.

EXECUTE sp_trace_setstatus

@traceid = @TraceID

,@status = 0

--Closes the trace. Status 2 corroponds to CLOSE.

EXECUTE sp_trace_setstatus

@traceid = @TraceID

,@status = 2

END

GO

6. Execute the following query to verify that the trace has been stopped and closed successfully. This query should not return a record for our trace if it is stopped and closed successfully.

--Verify the trace has been stopped and closed.

SELECT * FROM sys.traces

GO

7. The previous query will not return the row for the trace that we created because the trace has now been stopped and closed. Inspect the resulting trace data collected in our trace file by executing the following query:

--Retrieve the collected trace data.

SELECT

TE.name AS TraceEvent

,TD.DatabaseName

,TD.FileName

,TD.StartTime

,TD.EndTime

FROM fn_trace_gettable('C:\MyTraces\MyTestTrace.trc',default) AS

TD

LEFT JOIN sys.trace_events AS TE

ON TD.EventClass = TE.trace_event_id

GO

In this recipe, we first created and configured our trace by executing a T-SQL script. The script first declares some required variables whose values are passed as parameters to system stored procedures. It creates a trace by executing the sp_trace_create stored procedure that returns ID of the newly created trace. The stored procedure sp_trace_create accepts the following parameters:

• @traceid OUTPUT
• @options
• @tracefile

The @Options parameter is passed to specify the trace options. The following are the predefined values for the @Options parameter:

• 2: TRACE_FILE_ROLLOVER
• 4: SHUTDOWN_ON_ERROR
• 8: TRACE_PRODUCE_BLACKBOX

The parameter @TraceFile specifies the location and file name where the trace file should be saved. @TraceID is the output variable and the returned ID value of the trace will be stored in this variable. If the stored procedure can create a trace file successfully, it returns 0 that gets stored in variable @ReturnCode.

data columns by calling stored procedure sp_trace_setevent for each combination of event class and data column one-by-one for following event classes and data columns:

• DataFileAutoGrow event class and DatabaseName data column
• DataFileAutoGrow event class and FileName data column
• DataFileAutoGrow event class and StartTime data column
• DataFileAutoGrow event class and EndTime data column
• LogFileAutoGrow event class and DatabaseName data column
• LogFileAutoGrow event class and FileName data column
• LogFileAutoGrow event class and StartTime data column
• LogFileAutoGrow event class and EndTime data column

Stored procedure accepts the following parameters:

• @traceid
• @eventid
• @columnid
• @on

How to get IDs for all event classes and data columns?

ID values for required event classes and data columns must be passed to the stored procedure sp_trace_setevent. You can get a list of EventIDs for all event classes by querying sys.trace_events system catalog view. To get a list of column IDs for all data columns, use sys.trace_columns system catalog view. Also, you can retrieve list of column IDs for all available columns for a given event by querying sys.trace_event_bindings system catalog view and by joining it with sys.trace_events and sys.trace_columns system catalog views on trace_event_id and trace_
column_id columns respectively.

The value of @ on parameter value can be either 0 or 1 where the value 1 means that event data for specified event class and data column should be captured otherwise not.After adding the required event classes and data columns, the stored procedure sp_trace_setstatus is used to set the status of the trace to START. Any trace that is created with system stored procedure is always in STOP state by default, and needs to be started explicitly by calling sp_trace_setstatus stored procedure. This stored procedure accepts the following parameters:

• @traceid
• @status

@TraceID is the ID of the trace we created and need to be started. @Status specifies the state of the trace. Possible values for @Status parameter are as follows:

• 0: Stops a trace
• 1: Starts a trace
• 2: Closes a trace

Because we wanted to start our trace, we are passing a value of 1 to this parameter.

SQL Server keeps track of currently opened trace sessions. This list of traces can be retrieved by querying sys.traces system catalog view. We just make sure by querying this view that the trace is indeed created.

Next, we create a sample database named SampleDBTrace. We deliberately keep the value of FILEGROWTH attribute smaller in order to be able to produce Data File Auto Growth and Log File Auto Growth events. The script also creates a sample table named tbl_SampleData though SELECT … INTO statement in which we insert one million sample records by cross joining sys.columns system catalog view with itself multiple times. This operation requires additional space in data and log files to make room for inserting new records. For this, SQL Server has to increase the size of data and log files when required by one MB (specified value for the FILEGROWTH attribute). This causes DataFileAutoGrowth and LogFileAutoGrowth events to be raised.

Once the record insertion operation is completed, the script is executed to stop and close the trace by again calling the stored procedure sp_trace_setstatus twice with the appropriate status value for each call. Remember that to close a trace, it should be stopped first. So, a trace should be stopped first before it can be closed.After closing a trace, we make sure that the trace stopped and closed successfully by querying sys.traces system catalog view again.

Once our trace is stopped, we use fn_trace_gettable() function to query the captured trace data saved in specified trace file whose full file path is also being passed to the function for the first parameter filename. We also pass the default value for the second parameter number_files of the function which specifies that the function should read all rollover files to return trace data. Because this function does not return any column for the event class' name, we join it with sys.trace_events system catalog view on IDs of event classes in order to fetch the names of event classes.

If you want to analyze large size of trace data containing large number of trace files, then you should specify 1 for number_files parameter. If you specify default, the SQL Server tries to load all race files into memory and then inserts them into a table in a single operation, which may crash your system.