Thank you very much Himmat for your input.

GilaMonster,

Checking Long running queries make sense to me.

I was Wondering

if anything else could be checked, thanks for your input.

If I am not wrong, you can prevent windows admin to access sql server by disabling builtin admin in sql server.

Cheers,

DKG

Thanks Greg and Jack for all your help!!!

Jack,

It worked fine 🙂 the job stopped

ive query sys.traces and get the trace id and passed it into job....thanks a lot

Now if i want to split the trace files like...

Jack,

is that the stop job would contain just below statement(to stop the trace):

exec sp_trace_setstatus @traceid = traceid, @status = 0

Greg/Jack,

Your help would be appriciated...

Greg,

This time ive supplied

exec sp_trace_setstatus 0 or tried with exec sp_trace_setstatus '0' also

in the stop job, now it says

Message

Executed as user: NT AUTHORITY\SYSTEM. Procedure or Function 'sp_trace_setstatus' expects parameter...

Thanks Greg and Jack for your time and valuable suggestion it worked this time. Trace started automatically.

But I think ive done something wrong with the trace stop job. what i...

Ok, means all i need to create a trace file on some specific location and two sql agent jobs one would have sp_trace_setstatus trace_id, 1 - job having schedule to...

Greg,

yes i have specified exec @rc = sp_trace_create @TraceID output, 0, N'C:\trace_dev_test.trc', @maxfilesize, NULL

if (@rc != 0) goto error

where trace_dev_test.trc file exists.

in first run it gives trace...

Jack,

this peice of code contains sp_trace_create and the file name:

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

if (@rc != 0) goto error

Now please let me...

Greg, thanks for your input on this.

I have gone through the article and long discussion with in the article.

Seems useful, the script mentioned in the article is the output of...

This time im running it from quary analyser with sa and specified the path C:\temptrace.trc

"temptrace" is the trace file which i ve created manually from profiler.

in first execution is give...

Thanks Jack,

I have tried the script that creates the trace by going to File -> Export -> Script Trace Definition and select either 2000 or 2005.

and ran it throgh query...