USE master
GO
IF OBJECT_ID('dbo.usp_DeadlockTrace') IS NULL
EXEC('CREATE PROCEDURE dbo.usp_DeadlockTrace AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.usp_DeadlockTrace
AS /****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 04/28/2010 09:58:45 AM */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 10
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
DECLARE @ProfilePath VARCHAR(500)
SELECT @ProfilePath = ProfilePath
FROM Util.dbo.Servers
WHERE [SQLSERVERNAME] = @@SERVERNAME
DECLARE @FileName NVARCHAR(100)
SELECT @FileName = ISNULL(@ProfilePath, 'H:\Profiler\') + @@SERVICENAME + '-DeadlockTrace-'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ' ', '-'), ':', '')
--sp_trace_create [ @traceid = ] trace_id OUTPUT
-- , [ @options = ] option_value
-- , [ @tracefile = ] 'trace_file'
-- [ , [ @maxfilesize = ] max_file_size ]
-- [ , [ @stoptime = ] 'stop_time' ]
-- [ , [ @filecount = ] 'max_rollover_files' ]
--PRINT @FileName
EXEC @rc = sp_trace_create
@TraceID OUTPUT,
2 /* TRACE_FILE_ROLLOVER */,
@FileName,
@maxfilesize,
NULL,
10000
IF (@rc != 0)
GOTO ERROR
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent
@TraceID,
148,
11,
@on
EXEC sp_trace_setevent
@TraceID,
148,
12,
@on
EXEC sp_trace_setevent
@TraceID,
148,
14,
@on
EXEC sp_trace_setevent
@TraceID,
148,
1,
@on
-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
-- Set the trace status to start
EXEC sp_trace_setstatus
@TraceID,
1
-- display trace id for future references
SELECT TraceID = @TraceID
GOTO finish
ERROR:
SELECT ErrorCode = @rc
finish:
GO
GO
exec sp_procoption N'usp_DeadlockTrace', 'startup', 'on'
GO