Scheduled trace

[divinyl]

Hi all

I have a sql job that runs on about 10 servers in my company. It should normally take about 45 seconds to complete but on only one of these servers, it is taking up to 8 hours to run! I've checked everything I can think of as to what might be causing this but still haven't solved it. Now what i'd like to be able to do is schedule a trace to run on the server during the night when the job runs.

I;ve read up a bit on server side tracing (as i believe you cannot schedule profiler itself) but it sounds kinda complicated so i wondered if anyone can point me to some good sources of information regarding how to set this up, or whether anyone has done this before and can advise..

Thanks!
Div

 

[mrdenny]

The easiest way it to setup a trace. But before you start it going into the filter section and remove the default filter which blocks you from seeing SQL PRofiler. Then run the trace. After that trace is started setup a new trace with the settings you want to monitor. Run the second trace. Be sure to setup a file path to write to as well as a stop time.

Go back to the first profiler window and you will have all the code you need to schedule your trace via a SQL Job. (You may need to modify the file path and stop time).

Then stop the traces.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ambientguitar]

When doing a trace I usually use the following Method makes live easier all youhave to do is change the ini file.

--This will create a stored procedure to run a performance trace on the database
--it can be called with the following
--Parameters trace_build 'C:\TraceArgs.ini'
--The TraceArgs.ini file must be in the specified directory



CREATE proc trace_build @traceini nvarchar (245) = N'C:\ActivityTrace.ini' as
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint) from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime) from #t1 where left(c1,3) = '@st'
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int) from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '*.trc'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime

select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec sp_trace_setevent @traceid, @event, @column, @on
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter' + cast(@filter_num as nvarchar(3))
select @filter
end
finish:
drop table #t1
exec sp_trace_setstatus @traceid, 1


*******************************************************

Put the following in a text file and name traceargs.ini

*************************************************************


@tracefile = C:\ActivityTrace
@maxfilesize = 50
@stoptime = 2005-12-31 12:00:00.000
@options = 2
@events = 10,11,12,13,16,17,19,33,42,43,55
@columns = 1,2,3,6,9,10,11,12,13,14,15,16,17,18,25
@filter1 = 10, 0, 7, N'SQL Profiler'