--------------------------------------------------------------------------------
--  File        :   Stop and remove the server-side trace,2000.sql
--
--  Purpose     :   This script stops the server-side trace that is being used 
--                  to investigate deadlocks.
--
--  SQL Server  :   SQL Server 2000 (8.0)
--
--  Version     :   3.0009.0080.0000 (2012-05-10)
--------------------------------------------------------------------------------

USE [tempdb];

GO

/*  Declare and initialize local resources  */

--  The ID of the server-side trace.
DECLARE @TraceID Int;

--  Lookup the ID of the trace using the output file.
--  NOTE: If more than one trace has the work "deadlock" in the file name, the 
--  one with the lowest trace ID will be selected and stopped.  This is 
--  acceptable in sample code, but isn't appropriate for production systems.
SELECT @TraceID = Min([TraceID])
FROM
(
    SELECT [traceid] AS [TraceID],
        Cast([value] AS NVarChar(255)) AS [FileName]
    FROM ::fn_trace_getinfo(DEFAULT)
    WHERE ([property] = 2)
) AS [_TraceFiles]
WHERE ([FileName] LIKE N'%Deadlock trace%');

/*  Stop the trace  */

--  Verify that the trace ID was successfully determined.
IF (@TraceID IS NOT NULL)
BEGIN
    --  Stop the trace.
    EXECUTE [master].[dbo].[sp_trace_setstatus]
        @traceid = @TraceID,
        @status = 0;
    --  Delete the trace.
    EXECUTE [master].[dbo].[sp_trace_setstatus]
        @traceid = @TraceID,
        @status = 2;

    --  Output a status message.
    PRINT N'Trace ' +
        Cast(@TraceID AS NVarChar) +
        N' was successfully stopped.';
END
ELSE
BEGIN
	--  Raise an error.
    RAISERROR
    (
        N'An error occurred determining the ID of the the trace.',
        16,
        1
    );
END;

GO
