How to know if there is a trace is running on a SQL Server session

anushahanna
anushahanna used Ask the Experts™
on

Is it at all possible through t-SQL code to know if a trace is being run by the DBA on a session/instance/server?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
DBCC TRACESTATUS(-1);
GO
AneeshDatabase Consultant
Top Expert 2009
Commented:
the above will list the trances those are enabled globally, if you want the trace those are enabled for the current session use this
DBCC TRACESTATUS()

Author

Commented:
aneeshattingal,
will this work for SS2000, also?

I ran the above on a SS2000 server I know there is a trace running now, but it says
"Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'."
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2014
Commented:
The way to find out how many traces are running and the details:
-----------------------------------------------------------------------
SELECT * FROM :: fn_trace_getinfo(default)
-----------------------------------------------------------------------

There is a default trace that is always running in SQL 2005. So if you get two trace id's that means a second is running somewhere.

-----------------------------------------------------------------------
fn_trace_getinfo (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173875.aspx
Enable Trace at current connection level:
DBCC TRACEON(1205)

Disable Trace:
DBCC TRACEOFF(1205)

Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1205,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.
DBCC TRACEON (1205, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.
DBCC TRACESTATUS(-1)



Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial