sp_stop_trace

for RuBoard

Starting a trace using a stored procedure is nifty enough, but how do we stop it so that we can analyze the trace file? We use sp_stop_trace. Here's its code (Listing 21-5):

Listing 21-5 sp_stop_trace.

 USE master GO IF OBJECT_ID('sp_stop_trace') IS NOT NULL   DROP PROC sp_stop_trace GO CREATE PROC sp_stop_trace @TraceName sysname='tsqltrace' /* Object: sp_stop_trace Description: Stops a Profiler-like trace using Transact-SQL eXtended Procedure calls. Usage: sp_stop_trace @TraceName sysname default: tsqltrace -- Specifies the name of the trace Returns: (None) $Author: Ken Henderson $. Email: [e-mail] $Revision: 2.0 $ Example: EXEC sp_stop_trace -- Stops the default trace Created: 1999-04-01. $Modtime: 2000-12-16 $ */ AS SET NOCOUNT ON IF @TraceName='/?' GOTO Help -- Declare variables DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue DECLARE @OldTraceFile sysname -- File name of running trace -- Stop the trace if running IF OBJECT_ID('tempdb..TraceQueue') IS NOT NULL BEGIN   IF EXISTS(SELECT * FROM tempdb..TraceQueue WHERE TraceName = @TraceName) BEGIN     SELECT @OldQueueHandle = TraceID, @OldTraceFile=TraceFile     FROM tempdb..TraceQueue     WHERE TraceName = @TraceName     IF @@ROWCOUNT<>0 BEGIN       EXEC sp_trace_setstatus @traceid=@OldQueueHandle, @status=0       EXEC sp_trace_setstatus @traceid=@OldQueueHandle, @status=2       PRINT 'Deleted trace queue ' + CAST(@OldQueueHandle AS varchar(20))+'.'       PRINT 'The trace output file name is: '+@OldTraceFile       DELETE tempdb..TraceQueue WHERE TraceName = @TraceName     END   END ELSE PRINT 'No active traces named '+@TraceName+'.' END ELSE PRINT 'No active traces.' RETURN 0 Help: EXEC sp_usage @objectname='sp_stop_trace',@desc='Stops a Profiler-like trace using Transact-SQL eXtended Procedure calls.',                     @parameters='@TraceName sysname default: tsqltrace --                     Specifies the name of the trace ', @author='Ken Henderson', @email='[e-mail]', @version='2', @revision='0', @datecreated='19990401', @datelastchanged='20001216', @example='EXEC sp_stop_trace -- Stops the default trace ' RETURN -1 GO EXEC sp_stop_trace 

Because you can name traces when you start them, sp_stop_trace allows you to pass a trace name as a parameter. If you don't specify a trace name, it attempts to stop the default tracetsqltrace.

Note the use of the Tempdb..TraceQueue table by both routines. We need a way of tracking by name which traces are running, and we need one that will persist across disconnects. We could have used a plain temporary table, but it could be dropped as soon as we disconnected. We could have used a permanent table in a different databasemaster, for instance. However, that would unnecessarily clutter the server with unneeded permanent objects. After all, any traces we've started will stop automatically when the server stops. We don't need an object that persists across server shutdowns. What we need is a storage mechanism that persists as long as the currently running instance of SQL Server is running. We want one that goes away automatically when the server is restarted so that we don't mistakenly believe traces are running that, in fact, are not. This is why I chose to use a permanent table in Tempdb. Because Tempdb is recreated each time the server is restarted, it works perfectly for our purposes. The TraceQueue table represents the best of both worldsa temporary object that persists across connections and other user activity.

for RuBoard