Scheduling Server Side Trace (Profiler) For Deprecated Features

I was trying to use profiler to trace deprecated features in a SQL Server 2005, before it’s migrated from 2005 to 2008. And also capturing the use of deprecated features in different time period during a day was required, especially for those SQL queries that run over night. However SQL Server Profiler doesn’t provide a build-in scheduling option, so I decided to use SQL command to schedule an agent job, so that the profiler trace can automatically start.

Firstly, Use SQL Profiler to create the trace definition

1. Start SQL Profiler and select File > New Trace. Specify the events, columns, filters you want in your trace.
2. Start the trace and then stop it.
3. Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. Save the scripted trace as ‘*.sql’.
   Note: For SQL Sever 2000 and 2008 choose the appropriate output type.

Secondly, Create a store procedure to set up profiler trace.

Here, I am using tracing deprecated features as a sample.

/****************************************************/

/* Default Profiler Trace Template SQL Server 2005  */

/****************************************************/

CREATE PROCEDURE [dbo].[sp_deprecation_trace]

  @StopTime datetime 

AS

Declare @rc int

Declare @TraceID int

Declare @maxfilesize bigint

Declare @on bit

Declare @PhyName nvarchar(256)

Declare @StartTime varchar(12)

--Change @maxfilesize to whatever value you require (its in MB)

SET @maxfilesize = 30 SET @on = 1

SET @StartTime = CONVERT(varchar(12),GETDATE(),12) + REPLACE(CONVERT(varchar(12),GETDATE(),8),':','')

/***Create the name of the output file with trace starting time

     and don't append the .TRC to the path above it does it for you.****/

SET @PhyName = 'F:\Trace\Deprecated_' + @StartTime

--Create a trace

EXEC @rc = sp_trace_create @TraceID output, 2, @PhyName, @maxfilesize,@StopTime

-- If error end process

IF (@rc != 0) GOTO error

-- Select the return code to see if the trace creation was successful.

SELECT RC = @RC, TraceID = @TraceID

-- Trace definitions
-- copy & paste the definitions from the first section

-- or set up the events and data columns you need to capture manually

exec sp_trace_setevent @TraceID, 125, 63, @on

exec sp_trace_setevent @TraceID, 125, 8, @on

exec sp_trace_setevent @TraceID, 125, 64, @on

exec sp_trace_setevent @TraceID, 125, 1, @on

exec sp_trace_setevent @TraceID, 125, 9, @on

exec sp_trace_setevent @TraceID, 125, 61, @on

exec sp_trace_setevent @TraceID, 125, 6, @on

exec sp_trace_setevent @TraceID, 125, 10, @on

exec sp_trace_setevent @TraceID, 125, 14, @on

exec sp_trace_setevent @TraceID, 125, 3, @on

exec sp_trace_setevent @TraceID, 125, 11, @on

exec sp_trace_setevent @TraceID, 125, 12, @on

exec sp_trace_setevent @TraceID, 125, 22, @on

exec sp_trace_setevent @TraceID, 125, 34, @on

exec sp_trace_setevent @TraceID, 126, 63, @on

exec sp_trace_setevent @TraceID, 126, 8, @on

exec sp_trace_setevent @TraceID, 126, 64, @on

exec sp_trace_setevent @TraceID, 126, 1, @on

exec sp_trace_setevent @TraceID, 126, 9, @on

exec sp_trace_setevent @TraceID, 126, 61, @on

exec sp_trace_setevent @TraceID, 126, 6, @on

exec sp_trace_setevent @TraceID, 126, 10, @on

exec sp_trace_setevent @TraceID, 126, 14, @on

exec sp_trace_setevent @TraceID, 126, 3, @on

exec sp_trace_setevent @TraceID, 126, 11, @on

exec sp_trace_setevent @TraceID, 126, 12, @on

exec sp_trace_setevent @TraceID, 126, 22, @on

exec sp_trace_setevent @TraceID, 126, 34, @on

-- Set Filters

-- filter1 include databaseId = 6

EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

-- filter2 exclude application SQL Profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Start Trace (status 1 = start)

EXEC sp_trace_setstatus @TraceID, 1

-- Display trace id for future references 

SELECT TraceID=@TraceID 

GOTO finish 

-- error trap

error: 

SELECT ErrorCode=@rc 

finish:

go

Thirdly, Schedule a time to run the trace. As you can only specify the stop time in the SQL command, SQL Agent will help you to schedule a starting time.

EXEC dbo.sp_deprecation_trace @StopTime ='2011-09-14 11:14:00.000'

Finally, To output data from the trace file to a table 

I found if I just literary use the following script to output the date to the table, all the columns that were not required or captured in the trace file would still be created in the table.

--Load into a new table

SELECT * INTO temp_tablename

FROM ::fn_trace_gettable('F:\trace\mytrace_1.trc', default)

--Load into a new table

INSERT INTO temp_tablename

FROM ::fn_trace_gettable('F:\trace\mytrace_2.trc', default)

Therefore, I manually created a table with all the columns that only are set up in the store procedure and then insert data into the table. There is my script.

INSERT INTO Deprecation_Trace(

            EventClass

           ,TextData

           ,DatabaseID

           ,NTUserName

           ,HostName

           ,ClientProcessID

           ,ApplicationName

           ,LoginName

           ,SPID

           ,StartTime

           ,Offset

           ,SqlHandle

           ,SessionLoginName)

SELECT      EventClass

           ,TextData

           ,DatabaseID

           ,NTUserName

           ,HostName

           ,ClientProcessID

           ,ApplicationName

           ,LoginName

           ,SPID

           ,StartTime

           ,Offset

           ,SqlHandle

           ,SessionLoginName

FROM fn_trace_gettable

('F:\Trace\Deprecated_110914102741.trc', DEFAULT)

WHERE DatabaseID is not NULL and LoginName is not Null

ORDER BY StartTime

OK, all the jobs done before starting to analyse the captured data. This method can be used for all SQL Server Performance Statistics.