--------------------------------------------------------------------------------
--  File        :   Demo 06_Stop and remove the server-side trace,2005+.sql
--
--  Purpose     :   This script stops the server-side trace that is being used 
--                  to investigate deadlocks.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   3.0006.0090.0019 (2010-05-31)
--------------------------------------------------------------------------------

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 trace" 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([_Traces].[id])
FROM [sys].[traces] AS [_Traces]
WHERE ([_Traces].[path] 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].[sys].[sp_trace_setstatus]
        @traceid = @TraceID,
        @status = 0;
    --  Delete the trace.
    EXECUTE [master].[sys].[sp_trace_setstatus]
        @traceid = @TraceID,
        @status = 2;

    --  Output a status message.
    PRINT N'Trace ' +
        Cast(@TraceID AS NVarChar(11)) +
        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
