sql profiler stops collecting data

dear users,

sql server 2008 enterprise.

 

i set security logging through sql profiler sql server 2008 standard editions.

after created stored procedure , turned on autoexecution turn on sql profiler automatically after sql agent stops.

i set in september year , ran until 10/22/2010.

i experience twice far since set security logging.

i don't see trace stop message in sql profiler file.

i wonder stops logging information.

if @ line,

set @datetime = dateadd(d, 2000,getdate())

it should run 2000 days 6 years. i wonder why stops , should fix.

 

--for understanding attaching t-sql ran set logging. thank much.

use master

go

 

-- create queue

declare @rc int

declare @traceid int

declare @maxfilesize bigint

declare @datetime datetime

 

set @datetime = dateadd(d, 2000,getdate())

-- ‘d, 2000’ means trace runs 2000 days. can change number.

 

set @maxfilesize = 350

 

-- please replace text insertfilenamehere, appropriate

-- filename prefixed path, e.g., c:\myfolder\mytrace. .trc extension

exec @rc = sp_trace_create @traceid output, 2, n'c:\sqltrace\mytrace' , @maxfilesize, @datetime

if (@rc != 0) goto error

 

-- client side file , table cannot scripted

 

-- set events

declare @on bit

set @on = 1

exec sp_trace_setevent @traceid, 109, 7, @on

exec sp_trace_setevent @traceid, 109, 23, @on

exec sp_trace_setevent @traceid, 109, 39, @on

.

.

.

exec sp_trace_setevent @traceid, 113, 19, @on

exec sp_trace_setevent @traceid, 113, 35, @on

exec sp_trace_setevent @traceid, 113, 12, @on

 

 

-- set filters

declare @intfilter int

declare @bigintfilter bigint

 

exec sp_trace_setfilter @traceid, 10, 0, 7, n'sql server profiler - xxxxxxx'

-- set trace status start

exec sp_trace_setstatus @traceid, 1

 

-- display trace id future references

select traceid=@traceid

goto finish

 

error:

select errorcode=@rc

 

finish:

go

 

 

 

i found below message related trace logging in sql server log. message says somehow trace logging disabled. it says, "enable trace logging using traceflags registry key , examine adtagent.log in \temp subdirectory of windows directory."

 

do know how enable trace logging using traceflags registery key? couldn't find \temp subdirectory , couldn't find adt.agent.log.

 

forwarder unsuccessfully tried connect following collector(s):

 xxxxx.xxxx.xxxxx.xx.xx:xxxxx, status: 0x4c9 (tcp connect), source: registry

  addresses tried:

    xxx.xx.xxx.xxx:xxxxx

 

if list of collectors blank, adtagent unable locate collector.

 common reasons message are:

             the machine(s) listed not online

             adtserver not running on machine(s) listed

             adtserver on machine(s) listed not listening on specified port

             tcp connectivity adtserver machine blocked firewall, ipsec, or other filtering mechanism

             adtserver on machine(s) listed actively refused connection (due policy or current activity load)

 for detailed failure information, enable trace logging using traceflags registry key , examine adtagent.log in \temp subdirectory of windows directory

SQL Server  >  SQL Server Database Engine