I want to automate tracing for SQL server Profiler. I have written a set of
stored procedures using the SQL Server in-built sp's for the very purpose.
A trace is also being successfully created with trace id and a
file(MyTrace.trc) is also created however the trace is not writing anything
into the file MyTrace.trc
I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
and when accessing info of the trace using
SELECT *
FROM ::fn_trace_getinfo(@.TraceId)
it shows the correct file path and status as 1
Could some please help me out in this.
What else am i required to do for enabling server side trace and so that the
capture sql traces are written into the file.
Regards
Shajee
The easiest way to make sure you're on the right track is to use the
Profiler tool, get a trace running that is capturing what you want, stop it
and the script it (File>Script Trace). Then run the generated script and
stop it and check you have results. If this is all working okay then check
your code and make sure you don't have any filters present that might be
interfering with the capture. In SQL2000 you need to both stop the
serverside trace and delete its definition from the server in order to open
it correctly using Profiler or fn_trace_gettable. In SQL2005 this is no
lonfer the case and you can access a running server side trace. Are you
actually getting an error or is it simply that the trace file is empty. If
it is truly empty then it's likely an issue with your code - double check it
against the script generated by Profiler
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
|||Writes to the trace file happen in 128K chunks. Are you sure you've
generated that many events?
Try stopping and closing the trace (this takes to calls to
sp_trace_setstatus, first stop and then close the trace.
Or try generating lots of events to get 128K worth of data generated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
>
|||Thanks for the help. I really appreciate that.
The problem is resolved. Actually i was stopping the trace but not
closing it. So the captured events were not being written into
file(.trc). I'm using SQL 2000
Now after deleting the definition of trace by using
sp_trace_setstatus @.TraceId,2 it works and the specified file gets
update which's then accessed by function
::fn_trace_gettable(@.filepath,default)
Thanks for the help once again
Regards
Shajee
*** Sent via Developersdex http://www.codecomments.com ***
No comments:
Post a Comment