Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2012-10-24 : 17:22:38
|
| Good afternoon, i need your help plasesome days ago there is an ongoing procces which the sentence is: exec [master].dbo.sp_trace_getdata 8 asn is causing a strain to database serverso ive tried to find out which process is related to in order to stop and it has something to do with SQL Profiles.i killed the proccess many time sbut in a few seconss the Sql Profiler triggers the command againIm mot a DBA but ive got 2 questions:1. is there anyway to stop from running by denyng EXEC permissions to the user ?I identified the tracer and oen the SQL Profiler but the pay/stop buttons are disable2nd. i tested 2 select commands i found useful in an article posted how to stop it from running but didnt work out, maybe you can help me out:Execute "select * FROM ::fn_trace_getinfo(default)" - OKIdentify the traceid you using the folder name specified when starting the trace. OKTHE BELOW SENTENCES yieñded me an error:Execute these two commands replacing # with the trace id.EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause TraceEXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the serverServer: Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off. the above error doenst have to do with permissions or something likke that ?How can i solve it ?Thnaks a lot in advanced |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2012-10-24 : 17:41:09
|
| OK thanks, i mean a strain on the sarver because i notice performance problems on the ongoing producction sql processes which are running slower.2nd. How doesthe sp_trace_setstatus exactly work ? and is any additional paratemer to stop/close the user initiated traces ? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
how to stop SQL Profiler tracer from running