--------------------------------------------------------------------------------
--  File        :   Demo 07_Create a server-side trace to provide information on the next deadlock(s),2005+.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 2005 and 
--                  above.
--
--                  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 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   1.0005.0090.0000 (2010-02-02)
--------------------------------------------------------------------------------

USE [tempdb];

GO

/*  Declare and initialize local resources  */

--  Values used when checking the version of SQL Server running on the 
--  instance.
DECLARE @InstanceBuildNumber VarChar(20);
DECLARE @InstanceMajorVersionNumber Int;

--  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].[sys].[sp_trace_setevent] stored procedure.
DECLARE @On Bit;

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

--  The full path of the trace file.
DECLARE @TraceFilePath 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.1\MSSQL\LOG\Deadlock_'
--  A string representation of the date-time will be appended by this script, 
--  then the [master].[sys].[sp_trace_create] system stored procedure adds the 
--  file extension.
--  TODO - Initialize the following variable:
SET @TraceFilePath = N'E:\tnbarkhouse\Presentations\Presentation_SQLTrace\Scratch\' +
    Replace(@@ServerName, N'\', N'-') +
    N'_Deadlock trace_';

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

--  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;

/*  Determine the version of SQL Server for the current instance  */

SET @InstanceBuildNumber = Cast(ServerProperty('ProductVersion') AS VarChar);
SET @InstanceMajorVersionNumber = Cast(Left(@InstanceBuildNumber, (CharIndex('.', @InstanceBuildNumber) - 1)) AS Int);

/*  Configure the trace  */

BEGIN TRY
    --  Verify that the script is being run against a SQL Server 2005 or later 
    --  instance.
    IF (@InstanceMajorVersionNumber < 9)
    BEGIN
        RAISERROR
        (
            'This script is designed to be run in SQL Server 2005 (or later).',
            16,
            1
        );
    END;

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

    --  Verify that the trace was successfully created.
    IF
    (
        (@@Error = 0)
        AND
        (@ReturnCode = 0)
    )
    BEGIN
        --  Add the Lock:Deadlock event class.
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 1,  --  TextData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 2,  --  BinaryData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 3,  --  DatabaseID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 4,  --  TransactionID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 6,  --  NTUserName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 7,  --  NTDomainName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 8,  --  HostName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 9,  --  ClientProcessID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 10, --  ApplicationName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 11, --  LoginName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 12, --  SPID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 14, --  StartTime
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 22, --  ObjectID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 25, --  IntegerData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 26, --  ServerName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 32, --  Mode
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 35, --  DatabaseName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 41, --  LoginSid
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 49, --  RequestID
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 51, --  EventSequence
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 52, --  BigintData1
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 55, --  IntegerData2
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 56, --  ObjectID2
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 57, --  Type
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 58, --  OwnerID
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 60, --  IsSystem
            @on = @On;
        EXECUTE [master].[dbo].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 25,  --  Lock:Deadlock
            @columnid = 64, --  SessionLoginName
            @on = @On;

        --  Add the Lock:Deadlock Chain event class.
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 1,  --  TextData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 2,  --  BinaryData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 3,  --  DatabaseID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 4,  --  TransactionID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 12,  --  SPID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 14,  --  StartTime
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 21,  --  EventSubClass
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 22,  --  ObjectID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 25,  --  IntegerData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 26,  --  ServerName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 27,  --  EventClass
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 32,  --  Mode
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 35,  --  DatabaseName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 41,  --  LoginSid
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 49,  --  RequestID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 51,  --  EventSequence
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 56,  --  ObjectID2
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 57,  --  Type
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 58,  --  OwnerID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 60,  --  IsSystem
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 59,  --  Lock:Deadlock Chain
            @columnid = 64,  --  SessionLoginName
            @on = @On;

        --  Add the Deadlock Graph event class.
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 1,  --  TextData
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 11,  --  LoginName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 12,  --  SPID
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 14,  --  StartTime
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 26,  --  ServerName
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 27,  --  EventClass
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 41,  --  LoginSid
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 51,  --  EventSequence
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 60,  --  IsSystem
            @on = @On;
        EXECUTE [master].[sys].[sp_trace_setevent]
            @traceid = @TraceID,
            @eventid = 148,  --  Deadlock Graph
            @columnid = 64,  --  SessionLoginName
            @on = @On;

        --  Start the trace.
        EXECUTE [master].[sys].[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;
END TRY
BEGIN CATCH
    --  Output the error information.
    PRINT N'Error ' +
        Cast(Error_Number() AS VarChar) +
        N': ' +
        Error_Message();
END CATCH;

GO
