sqlserver死锁跟踪

DBCC errorlog（关闭当前错误日志，创建一个新的追踪日志）

DBCC TRACEON (1204, 1222, -1)3605 将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令
-1 以全局方式打开指定的跟踪标记

可以监控死锁将他传到日志中

二丶

create procedure  spm_Block
as


--查看阻塞和锁
SELECT 
  t1.request_session_id AS spid, 
  t1.resource_type AS type, 
  t1.resource_database_id AS dbid, 
  (case resource_type 
   WHEN 'OBJECT' THEN object_name(t1.resource_associated_entity_id) 
   WHEN 'DATABASE' THEN ' ' 
   ELSE (SELECT object_name(object_id) 
      FROM sys.partitions 
      WHERE hobt_id=resource_associated_entity_id) 
  END) AS objname, 
  t1.resource_description AS description, 
  t1.request_mode AS mode, 
  t1.request_status AS status,
   t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 
LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address 


--查找阻塞源头v3.0
SELECT SP.spid as '阻塞源头SPID'
      ,CASE WHEN ST1.text IS NULL THEN ST2.text
            ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2, 
                 (CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2 
                  ELSE SR.statement_end_offset END - SR.statement_start_offset)/2+1)
        END AS [T-sql]
      ,SP.loginame
      ,DB_NAME(SP.dbid) AS [db_name]
      ,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP with(nolock)
  LEFT JOIN SYS.DM_EXEC_REQUESTS SR with(nolock) ON SP.spid=SR.session_id
  LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC with(nolock) ON SP.spid=SC.session_id
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN(SELECT BLOCKED FROM SYS.SYSPROCESSES with(nolock) WHERE BLOCKED<>0)
  AND SP.BLOCKED=0