sp_list_trace

for RuBoard

Because the trace routines are designed to allow you to start the trace and leave it running even if you close the connection, it's possible that you'll need a means of checking the status of the currently running traces. This is what sp_list_trace is for. Here's its code (Listing 21-6):

Listing 21-6 sp_list_trace.
 USE master GO IF OBJECT_ID('sp_list_trace') IS NOT NULL   DROP PROC sp_list_trace GO CREATE PROC sp_list_trace @TraceId varchar(10)=NULL /* Object: sp_list_trace Description: Lists the currently running traces. Usage: sp_list_trace @TraceId -- the ID number of a previously started trace (optional) Returns: (None) $Author: Ken Henderson $. Email: khen@khen.com $Revision: 2.0 $ Example: EXEC sp_list_trace -- Lists the currently running traces Created: 1999-04-01. $Modtime: 2000-12-16 $. */ AS SET NOCOUNT ON IF @TraceId='/?' GOTO Help DECLARE @T int SET @T=CAST(@TraceId AS int) IF (OBJECT_ID('tempdb..TraceQueue') IS NOT NULL) BEGIN        IF (@T IS NULL) BEGIN              DECLARE tc CURSOR FOR SELECT * FROM tempdb..TraceQueue              FOR READ ONLY              DECLARE @tid int, @tname varchar(20), @tfile sysname              OPEN tc              FETCH tc INTO @tid, @tname, @tfile     IF @@ROWCOUNT<>0 BEGIN              WHILE @@FETCH_STATUS=0 BEGIN                     SELECT TraceId, TraceName, TraceFile                     FROM tempdb..TraceQueue                     WHERE TraceId=@tid                     SELECT * FROM ::fn_trace_getinfo(@tid)                     FETCH tc INTO @tid, @tname, @tfile              END     END ELSE PRINT 'No traces in the trace queue.'              CLOSE tc              DEALLOCATE tc       END ELSE BEGIN           SELECT TraceId, TraceName, TraceFile FROM tempdb..TraceQueue              WHERE TraceId=@T           SELECT * FROM ::fn_trace_getinfo(@T)   END END ELSE PRINT 'No traces to list.' RETURN 0 Help: EXEC sp_usage @objectname='sp_list_trace',@desc='Lists the currently running traces.', @parameters='@TraceId -- the ID number of a previously started trace (optional)', @author='Ken Henderson', @email='khen@khen.com', @version='2', @revision='0', @datecreated='19990401', @datelastchanged='20001216', @example='EXEC sp_list_trace -- Lists the currently running traces' RETURN -1 GO exec sp_list_trace 

(Results)

 TraceId     TraceName            TraceFile ----------- -------------------- ------------------------------------------ 4           tsqltrace            c:\TEMP\tsqltrace_20001204012356767 traceid     property    value ----------- ----------- --------------------------------------------------- 4           1           2 4           2           c:\TEMP\tsqltrace_20001204012356767 4           3           5 4           4           NULL 4           5           1 

Sp_list_trace calls the ::fn_trace_getinfo() system function to access key info for each trace. The table in Listing 21-6 with traceid as its first column is produced by ::fn_trace_getinfo(). You can take the trace name listed in the top table (which comes from Tempdb..TraceQueue) and pass it to sp_stop_trace to stop the trace if you like.

These routines present a viable alternative to the Profiler utility and may come in handy in your work. It wouldn't be difficult to combine the three of them into one master procedure so that you wouldn't have three procedures to keep track of. Again, this is an exercise best left to the reader.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net