--------------------------------------------------------------------------------
--  File        :   Create a server-side trace to provide information on the next deadlock(s),2000.sql
--
--  Purpose     :   This script creates a server-side trace that will record 
--                  two deadlock related event classes.  It can be used to 
--                  investigate recurring deadlocks in SQL Server 2000 (and, 
--                  technically, above... however, there is better version 
--                  for SQL Server 2005 and beyond).
--
--                  NOTE: This trace will exist until stopped manually or when 
--                  the instance shuts down/restarts.  Extra work would have 
--                  to done if one wanted it to start each time that the 
--                  instance starts up.
--
--                  To use this script, replace the values of the local 
--                  variables that are preceded by the "TODO" tokens.
--
--  SQL Server  :   SQL Server 2000 (8.0)
--
--  Version     :   3.0009.0080.0000 (2012-05-10)
--------------------------------------------------------------------------------

USE [tempdb];

GO

/*  Declare and initialize local resources  */

--  The maximum number of days that the trace should run, if it isn't stopped 
--  externally (manually or due to the instance shutting down).
DECLARE @MaximumDurationDays Int;

--  The maximum size, in megabytes, for the trace file.
DECLARE @MaximumFileSizeMB BigInt;

--  A bit variable to prevent data type promotion in the various calls to the 
--  [master].[dbo].[sp_trace_setevent] stored procedure.
DECLARE @On Bit;

--  The return code from the various database calls.
DECLARE @ReturnCode Int;

--  The name of the trace file.
DECLARE @TraceFileName NVarChar(245);

--  The ID by which the server-side trace will be accessed.
DECLARE @TraceID Int;

--  The time at which the trace should be automatically stop.  This is 
--  calculated based on the value of the @MaximumDurationDays variable.
DECLARE @TraceStopTime DateTime;

SET @On = 1;

--  It's a good idea to store the trace file in the log directory.  This 
--  ensures that file permissions won't be a problem.  An example of a good 
--  value would be:
--      'x:\Microsoft SQL Server\MSSQL\LOG\Deadlock_'
--  A string representation of the date-time will be appended by this script, 
--  then the [master].[dbo].[sp_trace_create] system stored procedure adds the 
--  file extension.
--  TODO - Initialize the following variable:
SET @TraceFileName = N'C:\Temp\' +
    Replace(@@ServerName, N'\', N'-') +
    N'_Deadlock trace_';

--  Append a string representation of the current time to the trace filename.
SET @TraceFileName = @TraceFileName +
    Replace(Replace(Replace(Convert(VarChar, Current_TimeStamp, 120), ' ', 'T'), '-', ''), ':', '');

--  Choose a value that is reasonable for the server being monitored.  Because 
--  the trace is so narrow, it is unlikely to grow very large, even with a 
--  large number of deadlocks.  Several megabytes should suffice.  At the same 
--  time, there's no harm in using a value like 128 since it is merely a cap 
--  and probably wouldn't be reached anyway.
--  TODO - Initialize the following variable:
SET @MaximumFileSizeMB = 512;

--  Choose a value based on the frequency of the deadlocks.  This is primarily 
--  in case the script is forgotten about on a highly available system, which 
--  shouldn't happen anyway.  Generally, a value of 7 to 21 days should be a 
--  good value.  Bear in mind that the trace may get stopped before this point 
--  if it reaches the maximum file size or if the instance shuts down/restarts.
--  TODO - Initialize the following variable:
SET @MaximumDurationDays = 7;

--  Calculate the stop time.
IF (@MaximumDurationDays < 0)
BEGIN
    --  A negative value for the @MaximumDurationDays variable means that 
    --  there shouldn't be an automatic stop time.
    SET @TraceStopTime = NULL;
END
ELSE
BEGIN
    --  Add the desired number of days to the current time.
    SET @TraceStopTime = DateAdd
    (
        day,
        @MaximumDurationDays,
        Current_TimeStamp
    );
END;

/*  Configure the trace  */

--  Create the trace.
EXECUTE @ReturnCode = [master].[dbo].[sp_trace_create]
    @traceid = @TraceID OUTPUT,
    @options = 0,
    @tracefile = @TraceFileName,
    @maxfilesize = @MaximumFileSizeMB,
    @stoptime = @TraceStopTime;

--  Verify that the trace was successfully created.
IF
(
    (@@Error = 0)
    AND
    (@ReturnCode = 0)
)
BEGIN
    --  Add the Lock:Deadlock event class.
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 1,  --  TextData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 2,  --  BinaryData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 3,  --  DatabaseID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 4,  --  TransactionID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 6,  --  NTUserName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 7,  --  NTDomainName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 8,  --  HostName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 9,  --  ClientProcessID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 10, --  ApplicationName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 11, --  LoginName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 12, --  SPID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 14, --  StartTime
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 22, --  ObjectID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 25, --  IntegerData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 26, --  ServerName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 32, --  Mode
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 35, --  DatabaseName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 25,  --  Lock:Deadlock
        @columnid = 41, --  LoginSid
        @on = @On;

    --  Add the Lock:Deadlock Chain event class.
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 1,  --  TextData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 2,  --  BinaryData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 3,  --  DatabaseID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 4,  --  TransactionID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 12,  --  SPID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 14,  --  StartTime
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 21,  --  EventSubClass
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 22,  --  ObjectID
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 25,  --  IntegerData
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 26,  --  ServerName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 27,  --  EventClass
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 32,  --  Mode
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 35,  --  DatabaseName
        @on = @On;
    EXECUTE [master].[dbo].[sp_trace_setevent]
        @traceid = @TraceID,
        @eventid = 59,  --  Lock:Deadlock Chain
        @columnid = 41,  --  LoginSid
        @on = @On;

    --  Start the trace.
    EXECUTE [master].[dbo].[sp_trace_setstatus]
        @traceid = @TraceID,
        @status = 1;

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

GO
