The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

sp_stop_trace

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: khen@khen.com
$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='khen@khen.com',
@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.