SQL Profiler vs Sys stored procedures

Question

Post reply

SQL Profiler vs Sys stored procedures

dbadotraghu

SSC-Addicted

Points: 438
More actions
September 14, 2010 at 9:44 am

#103984

Hi DBA's
I am trying to run a trace on SQL server 2000 named instance using System Sp's (Sp_create_trace, sp_add_event etc ..) the file is getting generated but its empty for ever, for some reason it is not writing anything to the file. If I run the profiler it is working jest fine. Please help, Thanks in advance
Thanks
Raghu

Viewing 10 posts - 1 through 9 (of 9 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Easy way out is script to script the profiler trace and run it on the instance. You may find this[/url] useful.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I agree with Pradeep, script it out from Profiler, and then see where you might be going wrong.

dbadotraghu SSC-Addicted Points: 438 More actions · Answer 3

Hi All

Sorry for a late response, i tired scripting out and run the same in management studio. Didn't work. any thoughts?

Adiga One Orange Chip Points: 27284 More actions · Answer 4

What is the error? The trace does not start at all?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

dbadotraghu SSC-Addicted Points: 438 More actions · Answer 5

No errors the status is running and file gets created but doesn't write anything to it. Trc file stays empty and 0 kb

Raghu

Adiga One Orange Chip Points: 27284 More actions · Answer 6

The size will not reflect correctly until the trace file is rolled over or the trace is stopped.

You may try stopping and closing the trace file to see if it is actually capturing information.

exec sp_trace_setstatus TraceIDHere, 0 --stop

exec sp_trace_setstatus TraceIDHere, 2 --close

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

dbadotraghu SSC-Addicted Points: 438 More actions · Answer 7

Hi Pradeep

As soon as I stop the trace, the file size grows to 128KB and the content of the trace file will be.. "The trace has been stopped". While the trace is running I have done lot of activity (create, Drop, alter, deny, revoke) but nothing was written. But as I mentioned earlier it captures everything when I run the profiler.

Thanks

Raghu

Adiga One Orange Chip Points: 27284 More actions · Answer 8

dbadotraghu (9/27/2010)
Hi Pradeep
As soon as I stop the trace, the file size grows to 128KB and the content of the trace file will be.. "The trace has been stopped". While the trace is running I have done lot of activity (create, Drop, alter, deny, revoke) but nothing was written. But as I mentioned earlier it captures everything when I run the profiler.

If the trace definition has the events to be captured, those events will be captured in the server side trace. Double check the events that needs to be traced and include them in the script

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

Whenever server face Memory crunch or performance issue then profiler skips some events because they require more resources to trace the event. If you want to capture all the events, you need server side tracing. This may help you.

Thanks