Creating trace with system stored procedures

What if you have no SQL Server Profiler installed on your machine and want to create a trace? What if you have SQL Server Profiler installed but the executable binary file of SQL Server Profiler is corrupted and cannot be run? What if you want to automate completely the process of capturing trace data as per your defined schedules so that you do not have to be physically present to start and stop the traces? Is it possible to create a trace in this manner without SQL Server Profiler?

The answer is yes. You can do this. SQL Server provides T-SQL system stored procedures to deal with SQL Trace. This capability enables us to write code that can create traces programmatically. By using SQL Trace system stored procedures along with SQL Agent, it is possible to automate and schedule the traces so that they run in background and capture event data during only certain period of time on a regular basis.

In this recipe, we will see how to create a trace without SQL Server Profiler by using SQL Trace system stored procedures. The trace that we will create in this recipe can be used to monitor the file growth of data files and log files of all databases on an instance of SQL Server. Monitoring file growth event for data files and log files will tell you how frequently your database files are grown that helps further in determining appropriate values for FILEGROWTH attribute of database files. If the size of files is increased by a smaller amount (for example, by 1 MB), SQL Server has to increase and extend the size of database files very frequently, which degrades the performance of write operations while working with large amount of data. It may also degrade the performance of read operations due to physical file fragmentation caused by small file chunks that are spread all over on the disk which makes a possible sequential read a random read. Thus, you should consider setting an appropriate FILEGROWTH value for your databases.

Getting ready

Before you start with the recipe, it is necessary that you have some background of basic system stored procedures provided in SQL Server which are used to work with traces. 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.

How to do it...

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
   

   Note

   It is possible that the stored procedure sp_trace_create may fail if the windows account under which the SQL Server Service is running has no write permission on the directory where the trace file is created. If this is the case, then you will need to assign proper permissions to the login account so that it can write to the specified directory.

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
   )
   LOG ON
   (
   NAME = N'SampeDBForTrace_log'
   ,FILENAME = N'C:\MyTraces\SampeDBForTrace_log.ldf'
   ,SIZE = 1024KB , FILEGROWTH = 1024KB
   )
   GO
   USE SampeDBForTrace
   GO
   --Creating and Inserting one million records tbl_SampleData table.
   SELECT TOP 1000000 C1.*
   INTO tbl_SampleData
   FROM sys.columns AS C1
   CROSS JOIN sys.columns AS C2
   CROSS JOIN sys.columns AS C3
   GO
   

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
   

How it works...

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.

Note

Remember that all SQL Trace system stored procedures are strictly typed. By saying strictly typed, it means that the data types of the parameters that you pass to these stored procedures must match exactly with the data types of stored procedures' parameter definition. So, you cannot pass a parameter of type INT when BIGINT is required.

If trace is created successfully and @ReturnCode is zero, then we add event classes and 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

@TraceID is the ID of the trace we add event classes and data columns to.

Note that every event classes and data columns have their associated event IDs and column IDs. We have to pass these ID values corresponding to event classes and data columns that we want to include in our trace. These values are passed by appropriate variables declared for each event class and data column. For example, for DataFileAutoGrow event class and FileName data column we have stored their appropriate ID values in @Event_DataFileAutoGrow and @DataColumn_FileName variables respectively.

Tip

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.

Note

We deliberately kept the value of the FILEGROWTH attribute as smaller as 1 MB in order to demonstrate this recipe. Setting value of the FILEGROWTH attribute this small is just for the sake of being able to produce the desired file growth events. Such small value for the FILEGROWTH attribute is not recommended and should not be used on production server with heavy DML operations.

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.

Tip

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 trace files into memory and then inserts them into a table in a single operation, which may crash your system.