Products
Webtrends Analytics 8.5
Webtrends Analytics 8.7
Webtrends Analytics 9.x
Cause
To ascertain whether or not an MS SQL trace is running, perform the steps below.
Resolution
The following queries will display this information, as well as how to start or stop the trace.
SELECT trcName.traceid as ‘Trace ID’,trcName.value as ‘Trace file’,CASE trcStatus.value
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
END as ‘Status’
FROM fn_trace_getinfo(default) trcName
JOIN fn_trace_getinfo(default) trcStatus
ON trcStatus.traceid = trcName.traceid
AND trcStatus.property = 5
WHERE trcName.property = 2
GO
To start or stop a trace, use the following syntax:
EXEC sp_trace_setstatus,
Example:EXEC sp_trace_setstatus 1,0
(Note that the default trace, traceID ?1?, cannot be started or stopped this way.)
The default SQL trace can be stopped via SP_CONFIGURE as follows:
EXEC master.dbo.sp_configure ‘allow updates’, 1;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
GO
EXEC master.dbo.sp_configure ‘default trace enabled’, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 0;
GO
EXEC master.dbo.sp_configure ‘allow updates’, 0;
GO
NOTE: These queries may need to be run manually, one by one, because some SQL versions report errors when you try to run them all at once. Each line can be run ignoring the following GO statement.
All of the above queries are executed with T-SQL, meaning they can be executed in the query window or command line, whichever is most convenient.