Need to run SQL Profiler from Batch Job
-
Does anyone have a documented procedure for setting up a SQL Profiler TRACE as a batch job?
(Currently, I manually setup a SQL PROFILER trace every morning - specifying DatabaseID, Events, StopTime, target trace output into a table, etc.. and would like to have this automated via as a scheduled job)
BT -
- you can you profiler to generate a trace-script to be executed trace-to-file.
Then schedule a sqljob to activate the trace.
You might even put the trace-part in a stored proc, if you don't have enough space in the sqljob.
e.g.
declare @StartStopEnd varchar(5)
declare @TraceID int
declare @RunDays smallint
declare @RunHours smallint
declare @RunMinutes smallint
declare @PlanNY char(1)
-- standaard voor 15 minuten ingesteld
Select @RunDays = 0
, @RunHours = 1
, @RunMinutes = 15
Select @StartStopEnd = 'Start', @TraceID = 0, @PlanNY = 'N'
-- Select @StartStopEnd = 'Stop', @TraceID = xxx
-- Select @StartStopEnd = 'End' --, @TraceID = xxx
if upper(@StartStopEnd )= 'START' goto StartTrace
else
begin
if upper(@StartStopEnd )= 'STOP' goto StopTrace
else
begin
if upper(@StartStopEnd )= 'END' goto CleanupTrace
else
begin
Print 'Ongeldige @StartStopEnd kode [' + @StartStopEnd + ']'
goto finish
end
end
end
goto finish
StartTrace:
-- Create a Queue
declare @rc int
-- declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = dateadd(dd,@RunDays,dateadd(hh,@RunHours,dateadd(mi,@RunMinutes,getdate())))
-- MB
set @maxfilesize = 5000 -- Mb
Declare @TraceFileName nvarchar(300)
set @TraceFileName = 'C:\ALZDBA_SQL_Trace' + '_' + replace(@@servername,'\','_') + '_' + replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') -- + '.trc' wordt automatisch toegevoegd
if datalength(@TraceFileName) > 299
begin
print 'Filename to long ! [' + cast( datalength(@TraceFileName) as varchar(5)) +'] - [' + cast(@TraceFileName as varchar(300)) +']'
goto finish
end
print '-- Saving tracedata to : ' + cast(@TraceFileName as varchar(300)) + ' --'
print '-- **********************' + REPLICATE('*', datalength(@TraceFileName)/2) + ' --'
print ' '
print '-- Trace will end at ' + convert(varchar(23),@DateTime,121) + '. --'
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, @TraceFileName, @maxfilesize, @Datetime
if (@rc <> 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 10, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 10, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 10, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 10, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 10, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 10, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 10, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 10, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 10, 31, @on -- Error
exec sp_trace_setevent @TraceID, 10, 40, @on -- DatabaseUsername
-- 11 = RPC:Starting
exec sp_trace_setevent @TraceID, 11, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 11, 3, @on -- DbId
exec sp_trace_setevent @TraceID, 11, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 11, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 11, 8, @on -- ClientHostname
exec sp_trace_setevent @TraceID, 11, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 11, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 11, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 11, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 11, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 11, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 11, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 11, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 11, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 11, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 11, 31, @on -- Error
exec sp_trace_setevent @TraceID, 11, 40, @on -- DatabaseUsername
-- 12 = SQL:BatchCompleeted
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 31, @on -- Error
exec sp_trace_setevent @TraceID, 12, 40, @on
-- 13 = SQL:BatchStarting
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 31, @on -- Error
exec sp_trace_setevent @TraceID, 13, 40, @on
-- 14 = Login
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 31, @on -- Error
exec sp_trace_setevent @TraceID, 14, 40, @on
-- 15 = Logout
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 31, @on -- Error
exec sp_trace_setevent @TraceID, 15, 40, @on
-- 17 = ExistingConnection
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 15, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 31, @on -- Error
exec sp_trace_setevent @TraceID, 17, 40, @on
if @PlanNY = 'Y'
Begin
-- 68 = obtain the execution plans
exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData
-- exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData
exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID
-- exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID
exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName
exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName
exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName
-- exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID
exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName
exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName
exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID
exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration
exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime
exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime
exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads
exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes
exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU
-- exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass
-- exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID
-- exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData
-- exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass
exec sp_trace_setevent @TraceID, 68, 26, @on
exec sp_trace_setevent @TraceID, 68, 31, @on -- Error
exec sp_trace_setevent @TraceID, 68, 40, @on
END
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, @status = 1
-- display trace id for future references
-- select TraceID=@TraceID
print '-- '
Print '-- TraceID for this trace [' + cast(@TraceID as varchar(10)) + '] --'
Print '-- ************************' + REPLICATE('*', datalength(cast(@TraceID as varchar(10)))) + '* --'
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default)
goto finish
StopTrace:
-- Set the trace status to stop
exec sp_trace_setstatus @TraceID, @status = 0
print 'Trace Stopped.'
goto finish
CleanupTrace:
-- Set the trace status to cleanup
exec sp_trace_setstatus @TraceID, @status = 2
print 'Trace Cleaned up.'
goto finish
error:
select ErrorCode=@rc
finish:
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Check this out:
http://www.mssqltips.com/tip.asp?tip=1035
Once you understand what is going on, the rest should be trivial
You'll probably want to have a job with 2 steps (once you adjust the code to your needs):
Step 1: Start Server Trace
Step 2: Stop and Remove Server Trace
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply