Scheduling SQL Profiler Trace

Hello,

Is there any way to schedule a SQL Profiler Trace? I know you can set a STOP time but I need to schedule bith START and STOP time of a SQL Profiler Trace.


Thanks,
Charlie_MelegaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sas13Commented:
use JOB with your schedule and next stored procedures:

sp_trace_create (Transact-SQL)     --> Creates a trace definition. The new trace will be in a stopped state.
sp_trace_setevent (Transact-SQL) --> Adds an event class or data column to a trace, or removes one from it.
sp_trace_setfilter (Transact-SQL)   --> Applies a new or modified filter to a trace.
sp_trace_setstatus (Transact-SQL)--> Starts, stops, or closes a trace.
chapmandewCommented:
use SQLDiag
BrandonGalderisiCommented:
I created a job in my database that I want to trace (most times I am looking at just one DB) and call it via a SSMS job hourly with the restart parameter.  You can see that you can also tell it to @stop or @Start.
CREATE PROCEDURE [dbo].[up_RestartTrace]
      @restart BIT = 1
     ,@stop    BIT = 0
     ,@start   BIT = 0
AS
SET NOCOUNT ON

DECLARE 
      @traceid           INT
     ,@dbid_filter       INT

IF @stop = 1 OR @start = 1
     SET @restart = NULL

IF @restart = 1
     SELECT @stop = 1, @start = 1

set @dbid_filter = DB_ID()

IF @stop = 1
BEGIN -- IF @stop = 1
     SET @traceid = -1

     WHILE @traceID IS NOT NULL
     BEGIN

          SELECT @traceid = traceid
          FROM :: fn_trace_getinfo(DEFAULT) 
          WHERE CAST(value AS VARCHAR(50)) LIKE 'd:\TraceData%DBID-' + LTRIM(STR(@dbid_filter)) + '%'

          IF @@rowcount = 0
               BREAK

          EXEC sp_trace_setstatus
                @traceid = @traceid
               ,@status = 0
          EXEC sp_trace_setstatus
                @traceid = @traceid
               ,@status = 2

     END

     SET @traceid = null
END -- IF @stop = 1

IF @start = 0
     RETURN 0



-- Starts Trace, presumably #1

DECLARE 
      /*@traceId           INT
     ,*/@traceFileName     sysname
     ,@dtPart            sysname
     ,@maxfilesizeMB     BIGINT
     ,@on                BIT
     ,@off               BIT
     ,@EventId           INT
     ,@cmd               sysname

SET @on = 1
SET @off = 0

SET @maxfilesizeMB = 20
SET @dtPart = RIGHT('00' + LTRIM(STR(YEAR(GETDATE()))) ,4)
            + RIGHT('00' + LTRIM(STR(month(GETDATE()))) ,2)
            + RIGHT('00' + LTRIM(STR(day(GETDATE()))) ,2)
            + RIGHT('00' + LTRIM(STR(datepart(hh,GETDATE()))),2) 
            + RIGHT('00' + LTRIM(STR(datepart(n,GETDATE()))),2) 
            + RIGHT('00' + LTRIM(STR(datepart(s,GETDATE()))),2)



SET @cmd = 'md d:\TraceData\DBID-' + LTRIM(STR(@dbid_filter))
SET @cmd = 'md d:\TraceData\DBID-' + LTRIM(STR(@dbid_filter)) + '\' + @dtPart
EXEC master..xp_cmdshell @cmd
SET @traceFileName = 'd:\TraceData\DBID-' + LTRIM(STR(@dbid_filter)) + '\' + @dtPart + '\Trace_DB-' + LTRIM(STR(@dbid_filter))+ '_' + @dtPart + '.trc'

EXEC sp_trace_create 
      @traceId = @traceId OUTPUT
     ,@options = 2
     ,@traceFile = @traceFileName 
     ,@maxfilesize = @maxfilesizeMB

SELECT @traceid AS '@traceid'

--Turn on RPC
SET @EventId = 10
EXEC sp_trace_setevent
      @traceid = @traceId
     ,@eventID = @eventId
     ,@columnid = NULL
     ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 1    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 3    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 4    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 8    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 10   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 12   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 13   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 14   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 15   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 16   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 17   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 18   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 22   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 24   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 28   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 29   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 32   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 34   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 35   ,@on = @on



--Turn on SQL Batch Complete
SET @EventId = 12
EXEC sp_trace_setevent
      @traceid = @traceId
     ,@eventID = @eventId
     ,@columnid = NULL
     ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 1    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 3    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 4    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 8    ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 10   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 12   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 13   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 14   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 15   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 16   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 17   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 18   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 22   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 24   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 28   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 29   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 32   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 34   ,@on = @on
          EXEC sp_trace_setevent @traceid = @traceId,@eventID = @eventId,@columnid = 35   ,@on = @on



--Filter DB to 5 
EXEC sp_trace_setfilter 
      @traceid = @traceId
     ,@columnId = 3
     ,@logical_operator = 0
     ,@comparison_operator = 0
     ,@value = @dbid_filter

--Start the trace
EXEC sp_trace_setstatus
      @traceid = @traceid
     ,@status = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Charlie_MelegaAuthor Commented:
This worked perfectly, kudos!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.