Profile Trace

Hello,
We have a profile trace set up on our server, however we
would like to cut it down into smaller traces.
What we were wondering then was if we can run
the "Template Load", "Trace saved to DB", "New Trace
Name", "Start Trace" & "Stop Trace" from SQL Server agent.
If the answer is "Yes", can someone point me to the actual
commands ?
I have looked on BOL but I can't find them.
Thanks
PeterThe best thing to do would be to run the trace programmatically from SQL
Server, not from SQL Profiler. This will cause less overhead as well.
You can define the trace in Profiler, then generate a SQL script with your
trace definition from within Profiler by choosing File|Script Trace... from
the menu and run the script from Query Analyzer.
In your trace definition, set it to Save to File, set maximum file size to 1
MB, and set enable file rollover on. You can now access the files
separately, (they will be numbered consequetively) from either Profiler or
from QA with the function fn_trace_get_table. You can't access the trace
file that is currently being written though.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote in message
news:079d01c349f4$5edebf50$a101280a@.phx.gbl...
> Hello,
> We have a profile trace set up on our server, however we
> would like to cut it down into smaller traces.
> What we were wondering then was if we can run
> the "Template Load", "Trace saved to DB", "New Trace
> Name", "Start Trace" & "Stop Trace" from SQL Server agent.
> If the answer is "Yes", can someone point me to the actual
> commands ?
> I have looked on BOL but I can't find them.
> Thanks
> Peter|||Thank you for your response.
however can you tell me how to start and stop it ?
Thanks
Peter
>--Original Message--
>The best thing to do would be to run the trace
programmatically from SQL
>Server, not from SQL Profiler. This will cause less
overhead as well.
>You can define the trace in Profiler, then generate a SQL
script with your
>trace definition from within Profiler by choosing
File|Script Trace... from
>the menu and run the script from Query Analyzer.
>In your trace definition, set it to Save to File, set
maximum file size to 1
>MB, and set enable file rollover on. You can now access
the files
>separately, (they will be numbered consequetively) from
either Profiler or
>from QA with the function fn_trace_get_table. You can't
access the trace
>file that is currently being written though.
>
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote in
message
>news:079d01c349f4$5edebf50$a101280a@.phx.gbl...
>> Hello,
>> We have a profile trace set up on our server, however we
>> would like to cut it down into smaller traces.
>> What we were wondering then was if we can run
>> the "Template Load", "Trace saved to DB", "New Trace
>> Name", "Start Trace" & "Stop Trace" from SQL Server
agent.
>> If the answer is "Yes", can someone point me to the
actual
>> commands ?
>> I have looked on BOL but I can't find them.
>> Thanks
>> Peter
>
>.
>|||To start:
sp_trace_setstatus @.trace_id = <trace_id>, @.status = 1
To stop:
sp_trace_setstatus @.trace_id = <trace_id>, @.status = 0
You get to trace_id as the output parameter when you create the trace with
sp_trace_create. Traces do not have names, so if you have forgotten the
trace_id, you have to find it back from the output of fn_trace_getinfo,
which shows all the traces on the server and the file they are written to.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote in message
news:03d101c34a01$e1c9b020$a001280a@.phx.gbl...
> Thank you for your response.
> however can you tell me how to start and stop it ?
> Thanks
> Peter
> >--Original Message--
> >The best thing to do would be to run the trace
> programmatically from SQL
> >Server, not from SQL Profiler. This will cause less
> overhead as well.
> >
> >You can define the trace in Profiler, then generate a SQL
> script with your
> >trace definition from within Profiler by choosing
> File|Script Trace... from
> >the menu and run the script from Query Analyzer.
> >
> >In your trace definition, set it to Save to File, set
> maximum file size to 1
> >MB, and set enable file rollover on. You can now access
> the files
> >separately, (they will be numbered consequetively) from
> either Profiler or
> >from QA with the function fn_trace_get_table. You can't
> access the trace
> >file that is currently being written though.
> >
> >
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
> >"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote in
> message
> >news:079d01c349f4$5edebf50$a101280a@.phx.gbl...
> >> Hello,
> >>
> >> We have a profile trace set up on our server, however we
> >> would like to cut it down into smaller traces.
> >>
> >> What we were wondering then was if we can run
> >> the "Template Load", "Trace saved to DB", "New Trace
> >> Name", "Start Trace" & "Stop Trace" from SQL Server
> agent.
> >> If the answer is "Yes", can someone point me to the
> actual
> >> commands ?
> >>
> >> I have looked on BOL but I can't find them.
> >>
> >> Thanks
> >> Peter
> >
> >
> >.
> >|||Thanks Jacco, you are a life saver.
Peter
>--Original Message--
>To start:
>sp_trace_setstatus @.trace_id = <trace_id>, @.status = 1
>To stop:
>sp_trace_setstatus @.trace_id = <trace_id>, @.status = 0
>You get to trace_id as the output parameter when you
create the trace with
>sp_trace_create. Traces do not have names, so if you have
forgotten the
>trace_id, you have to find it back from the output of
fn_trace_getinfo,
>which shows all the traces on the server and the file
they are written to.
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote in
message
>news:03d101c34a01$e1c9b020$a001280a@.phx.gbl...
>> Thank you for your response.
>> however can you tell me how to start and stop it ?
>> Thanks
>> Peter
>> >--Original Message--
>> >The best thing to do would be to run the trace
>> programmatically from SQL
>> >Server, not from SQL Profiler. This will cause less
>> overhead as well.
>> >
>> >You can define the trace in Profiler, then generate a
SQL
>> script with your
>> >trace definition from within Profiler by choosing
>> File|Script Trace... from
>> >the menu and run the script from Query Analyzer.
>> >
>> >In your trace definition, set it to Save to File, set
>> maximum file size to 1
>> >MB, and set enable file rollover on. You can now access
>> the files
>> >separately, (they will be numbered consequetively) from
>> either Profiler or
>> >from QA with the function fn_trace_get_table. You can't
>> access the trace
>> >file that is currently being written though.
>> >
>> >
>> >
>> >--
>> >Jacco Schalkwijk MCDBA, MCSD, MCSE
>> >Database Administrator
>> >Eurostop Ltd.
>> >
>> >
>> >"Peter" <pnolan@.REMOVETHECAPITALSpinnacle.co.uk> wrote
in
>> message
>> >news:079d01c349f4$5edebf50$a101280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We have a profile trace set up on our server,
however we
>> >> would like to cut it down into smaller traces.
>> >>
>> >> What we were wondering then was if we can run
>> >> the "Template Load", "Trace saved to DB", "New Trace
>> >> Name", "Start Trace" & "Stop Trace" from SQL Server
>> agent.
>> >> If the answer is "Yes", can someone point me to the
>> actual
>> >> commands ?
>> >>
>> >> I have looked on BOL but I can't find them.
>> >>
>> >> Thanks
>> >> Peter
>> >
>> >
>> >.
>> >
>
>.
>