Any way to tell what traces are running?

I have a stored proc where I create a SQL trace (using sp_trace_create). I
went onto one of my servers to fire off a trace and noticed that another one
has been running for a few days.
Is there any way to display the traces that are running on a particular
server? I would like to stop the old trace but I can't figure out how
Any help would be appreciated.
Thanks in advance.
TomIt figures - I was looking for this info all day yesterday. Right after
posting this, I finally discovered it.
The following SQL statement displays info on traces running
SELECT * FROM :: fn_trace_getinfo(default)
It seems that when I started the new trace, it was assigned TraceId=1 and
the old trace was bumped to TraceId=2. I then just issued the following to
kill trace 2:
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#MUmQPzuDHA.1088@.tk2msftngp13.phx.gbl...
> I have a stored proc where I create a SQL trace (using sp_trace_create).
I
> went onto one of my servers to fire off a trace and noticed that another
one
> has been running for a few days.
> Is there any way to display the traces that are running on a particular
> server? I would like to stop the old trace but I can't figure out how
> Any help would be appreciated.
> Thanks in advance.
> Tom
>|||You can run from QueryAnalyzer statement DBCC TRACESTATUS (-1) , and you will see all traces that have been set
Something like
TraceFlag TraceStatu
1204
Hope it Helps

Any way to run a invisible trace on Security Audit?

Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.Yes, you can configure SQL 2000 Auditing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2ard.asp), up through the C2 level. Unfortunately there isn't a "cookbook" approach for doing it, you really need to understand what you are doing, and the consequences and benefits of each decision.

-PatP|||I found out . Thanks for reply. C2 auditing however is not needed . I found out that from SQL Profiler , you can script the whole trace as SQL and execute it in ISQLW . This gives back a Trace ID . Next you have to run sp_trace_Setstatus @.traceID, 1 to run it .

Thanks for responding . I appreciate that

Any help on sp_trace_getdata

Hi
I have created a trace and then I tried reading trace through
sp_trace_getdata by specifying the correct trace id.
But query analyser become busy in continously executing sp_trace_getdata and
does return any result for long time.
Can any one suggest how should I call sp_trace_getdata to get the result
asynchronously, of the trace as when it is generated.
Thanks
Pushkar
Are you trying to consume the trace rowset? If you are using SQL Query
Analyzer, make sure you are not sending it to the grid, but to the text
window.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:%23pJuSu3ZFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi
> I have created a trace and then I tried reading trace through
> sp_trace_getdata by specifying the correct trace id.
> But query analyser become busy in continously executing sp_trace_getdata
> and
> does return any result for long time.
> Can any one suggest how should I call sp_trace_getdata to get the result
> asynchronously, of the trace as when it is generated.
> Thanks
> Pushkar
>
|||Thanks a lot Gert.
When sending the output to the text it worked. But why it was not working
while sending the output to a grid?
How can I write a application or extended stored procedure that can consume
the trace rowset and update some table?
Please help me out.
Thanks a lot.
Pushkar
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:uiN6eVBaFHA.464@.TK2MSFTNGP15.phx.gbl...
> Are you trying to consume the trace rowset? If you are using SQL Query
> Analyzer, make sure you are not sending it to the grid, but to the text
> window.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:%23pJuSu3ZFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
|||Because the grid will consume rows until it gets end of rowset, which in
case of SQL Trace will only happen when you stop the trace.
This is a behavior of the grid in Query Analyzer, OSQL and when using the
text window will pull rows directly and display them, like profiler does,
you can do exactly the same thing.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u1oS9BCaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot Gert.
> When sending the output to the text it worked. But why it was not working
> while sending the output to a grid?
> How can I write a application or extended stored procedure that can
> consume
> the trace rowset and update some table?
> Please help me out.
> Thanks a lot.
> Pushkar
>
>
>
> "Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
> news:uiN6eVBaFHA.464@.TK2MSFTNGP15.phx.gbl...
> rights.
>
|||Thanks
Pushkar
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eSPrx6HaFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Because the grid will consume rows until it gets end of rowset, which in
> case of SQL Trace will only happen when you stop the trace.
> This is a behavior of the grid in Query Analyzer, OSQL and when using the
> text window will pull rows directly and display them, like profiler does,
> you can do exactly the same thing.
> --
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:u1oS9BCaFHA.3864@.TK2MSFTNGP10.phx.gbl...
working
>

any dba checklist

hi,
1. any dba checklist information available on the net, e.g. daily operation,
weekly or monthly
2. will the dba switch on the server-side trace during product to monitor
any abnormal behaviour? if yes, any examples?
thanks a lot!
Hi
To some extent your check list will be determined by the system you are
running and how it is configured.
This site is a good resource of information
http://www.sql-server-performance.co...l_by_date.asp, You may also
want to look at the operations guide at
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
A list of useful resources can be found at
http://www.aspfaq.com/show.asp?id=2423
Books online is also a valuable resource and if you look up sqldiag and
blackbox you may find the answer to your second question.
John
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uo2kmFC5EHA.2540@.TK2MSFTNGP09.phx.gbl...
> hi,
> 1. any dba checklist information available on the net, e.g. daily
> operation,
> weekly or monthly
> 2. will the dba switch on the server-side trace during product to monitor
> any abnormal behaviour? if yes, any examples?
> thanks a lot!
>
>
>

any dba checklist

hi,
1. any dba checklist information available on the net, e.g. daily operation,
weekly or monthly
2. will the dba switch on the server-side trace during product to monitor
any abnormal behaviour? if yes, any examples?
thanks a lot!Hi
To some extent your check list will be determined by the system you are
running and how it is configured.
This site is a good resource of information
http://www.sql-server-performance.com/articles_all_by_date.asp, You may also
want to look at the operations guide at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
A list of useful resources can be found at
http://www.aspfaq.com/show.asp?id=2423
Books online is also a valuable resource and if you look up sqldiag and
blackbox you may find the answer to your second question.
John
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uo2kmFC5EHA.2540@.TK2MSFTNGP09.phx.gbl...
> hi,
> 1. any dba checklist information available on the net, e.g. daily
> operation,
> weekly or monthly
> 2. will the dba switch on the server-side trace during product to monitor
> any abnormal behaviour? if yes, any examples?
> thanks a lot!
>
>
>