Can i schedule profiler into sql server

Question

Can i schedule profiler into sql server

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You have to provide sp_trace_setstatus 2 parameters. Parameter 1 is the traceid and parameter 2 is the status. So you need:

sp_trace_setstatus @traceid = traceid, @status = 0

to stop a trace,

sp_trace_setstatus @traceid = traceid, @status = 1

to start a trace, and

sp_trace_setstatus @traceid = traceid, @status = 2

to stop a trace and remove the definition from the server.

You can get the traceid by querying sys.traces.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 2

Jack,

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

exec sp_trace_setstatus @traceid = traceid, @status = 0

DKG Ten Centuries Points: 1183 More actions · Answer 3

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 we can set it into gui ->Set Maximum file size(MB) = 100/200 e.g.

can i pass it - in place of @maxfilesize. Would it be in MB bydefault and like '100'

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

if (@rc != 0) goto error

please suggest....

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I'm glad we got you up and running. Now it's time for you to dive into BOL (Books On Line) and read up on sp_trace_create. It has all the information you need to set the file size and number of rollover files.

We are now in "Give a man a fish and you feed him for a day, teach a man to fish and you feed him for his life" territory.

Look it up and if there is something in BOL you don't understand post it an we'll help you understand it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 5

DKG

Ten Centuries

Points: 1183

July 31, 2008 at 1:16 am

#851024

Thanks Greg and Jack for all your help!!!

Himmat Singh SSC Enthusiast Points: 164 More actions · Answer 6

this may help

How to run profiler in batch mode:

start profiler and create new trace -- click - save to file, roll over & max. file size 100 meg then select evets and run it for few

minutes and stop it.

1) file/save or export script trace defination for sql200/5 (creates *.sql file) and edit this file these -

a) set @maxfilesize = 100 b) InsertFileNameHere to 'e:\sqlbku\monitor\file_name'

2) Run/schedule this file in query analyzer and note the trace id to stop this trace later e.g. traceid = 1 .

3) To stop the trace edit *.sqlfile exec sp_trace_setstatus @TraceID, 1 to exec sp_trace_setstatus 1, 2 and run this command in saparate

query analyzer exec sp_trace_setstatus 1, 2 to stop the trace.

research sp_trace_create

DKG Ten Centuries Points: 1183 More actions · Answer 7

DKG

Ten Centuries

Points: 1183

August 1, 2008 at 12:07 pm

#852082

Thank you very much Himmat for your input.