September 22, 2004 at 4:31 am
Hi everybody,
I am working on SQL server trace using stored procedures. I am able to create a trace using the command "sp_trace_create ..". I am also able to view the trace after setting status "sp_trace_setstatus.." .
The problem i am facing is that in the evening after the system running trace is shut down and restarted the next day the traceId cannot be obtained by "::fn_trace_getinfo(0)" though the trace is found running. Hence the trace cannot be read.
The trace cannot be saved to a table via SP's. I have also ried saving to a file with the same result.
Could somebody help me out..
Regards and thanks in advance..
October 1, 2004 at 8:23 pm
I use a set of stored procedures written by Narayana Vyas Kondreddi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
I then rap those into a stored procedure and call them from a job that rotates them every hour.
This example starts a trace and stops the same trace when called again.
--------------------------------------------------------------------------------------------------
--script to start and rotate Long Running Stored Procs trace--
--this could be rappped up into a stored procedure and set to start and sever startup using --
--sp_procoption--
--------------------------------------------------------------------------------------------------
declare @date varchar(255)
--holds current date
declare @time varchar(255)
--holds current time
declare @cmd as varchar(4000)
--holds any commands issued via xp_cmdshell
declare @trcpath as varchar(4000)
--files for traces stored here
declare @svrname as varchar(255)
--the server name
select @svrname = cast(serverproperty('servername') as varchar(255))
--populate servername
--check to see if it is an instance name and correct for the slash
if charindex('\',@svrname,1) > 0
begin
set @svrname = replace(@svrname,'\','_')
end
set @trcpath = 'd:\sqltraces\'+@svrname+'_LongRunningSprocs'
--set trace file name
select @date = convert(varchar,getdate(),112)
select @time = replace(convert(varchar,current_timestamp,114),':','')
select @time = left(@time,4)
--parce timestamp
SELECT * into #traces FROM :: fn_trace_getinfo(default)
--get running traces
select distinct traceid into #stopt from #traces where traceid = (select distinct traceid from #traces where cast(value as varchar(255)) like '%LongRunningSprocs%')
declare @traceid int
while (select count(traceid) from #stopt) > 0
begin
set @traceid = (select top 1 traceid from #stopt)
print @traceid
EXEC master.dbo.StopTrace @traceid
EXEC master.dbo.ClearTrace @traceid
--kill any traces that match our criteria
delete from #stopt where traceid = @traceid
set @cmd='move "'+@trcpath+'.trc" "'+@trcpath++@date+@time+'.trc"'
--rename finished trace file
exec master..xp_cmdshell @cmd,no_output
end
drop table #traces
drop table #stopt
EXEC master.dbo.CreateTrace
@trcpath,
@OverwriteFile = 1,
@OutputTraceID = @TraceID OUT
EXEC master.dbo.AddEvent
@TraceID,
'SP:Completed',
'StartTime,TextData,NTUserName,ClientHostName,ApplicationName,SQLSecurityLoginName,DatabaseID,,Duration'
EXEC master.dbo.AddFilter
@TraceID,
'Duration',
15000,
'>='
EXEC master.dbo.StartTrace @TraceID
--restart trace
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply