蓓姬

1. 限制:

必須開啟Trace Flags 1222才會寫ErrorLog, 可配合sp_cycle_errorlog產生新的ErrorLog

Deadlock必須返回錯誤, 才會寫ErrorLog, 但一般若沒設Deadlock Priority或Timeout, Process們會互咬不放, 根本不會返回錯誤

sp_cycle_errorlog
DBCC TRACEON (1222,-1)

 

2. 模擬Deadlock 

--Session1
SET DEADLOCK_PRIORITY HIGH
BEGIN TRAN
	UPDATE LinkedUUTQSMC SET Work_Order='201162848-1' WHERE SN='1050QCQ06B'
	WAITFOR DELAY '00:01' 
	UPDATE LinkedUUTQSMC SET Work_Order='201162848-1' WHERE SN='QTFCQK14240099'
COMMIT TRAN

 

--Session2
BEGIN TRAN
	UPDATE LinkedUUTQSMC SET Work_Order='201162848-1' WHERE SN='QTFCQK14240099'
	UPDATE LinkedUUTQSMC SET Work_Order='201162848-1' WHERE SN='1050QCQ06B'
COMMIT TRAN

 

3. 取得ErrorLog

SQL Server 2012的ErrorLog位於C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG

 

4. 分析ErrorLog

 

5. 找出被鎖定的table, index, value

--1. 找出objectname=CompData.dbo.LinkedUUTQSMC
--2. 找出hobtid=72057594039500800, associatedObjectId=72057594039500800
--3. 找出waitresource=KEY:6:72057594039500800(22c1ba5a1cc7)

--4. 用table找出被鎖定的index : PK_LinkedUUTQSMC
SELECT [index]=name, [type]=type_desc FROM sys.indexes WHERE object_id=object_id('CompData.dbo.LinkedUUTQSMC')

--5. 用hobtid或associatedObjectId找出被鎖定的table,index : LinkedUUTQSMC, PK_LinkedUUTQSMC
SELECT [table]=OBJECT_NAME(i.object_id), [index]=i.name FROM sys.partitions p, sys.indexes i WHERE i.object_id=p.object_id AND i.index_id=p.index_id AND p.partition_id=72057594039500800

--6. 用table,index,lockres找出被鎖定的value: 1050QCQ06B
SELECT lockres=%%lockres%%, CompData.dbo.LinkedUUTQSMC.* FROM CompData.dbo.LinkedUUTQSMC WITH(INDEX(PK_LinkedUUTQSMC)) WHERE %%lockres%% ='(22c1ba5a1cc7)'

--7. 用table,index,value找出被鎖定的page : (1:505800:0)
SELECT physloc=%%physloc%%,[page]=sys.fn_physlocformatter(%%physloc%%),CompData.dbo.LinkedUUTQSMC.* FROM CompData.dbo.LinkedUUTQSMC WITH(INDEX(PK_LinkedUUTQSMC)) WHERE SN='1050QCQ06B'  
 
--8. 印出page詳細資訊
DBCC TRACEON(3604)
DBCC PAGE (6,1,505800,1) --DBCC PAGE (dbid,fileid,pageid,1)

 

6. 程式化分析ErrorLog

CREATE FUNCTION [dbo].[ufn_ErrorLogToTable](
    @Text VARCHAR(8000), 
	@FieldTerminator VARCHAR(8000),
	@RowTerminator VARCHAR(8000)
) RETURNS @Table TABLE (Stmt VARCHAR(8000))

BEGIN

DECLARE @FieldTerminatorPosition INT
DECLARE @RowTerminatorPosition INT
DECLARE @Item VARCHAR(8000)=''
DECLARE @Value VARCHAR(8000)=''

--單引號'
IF CHARINDEX('''',@Text)<>0 
BEGIN
	INSERT @Table SELECT @Text
	RETURN
END

--@
IF CHARINDEX('@',@Text)<>0 
BEGIN
	INSERT @Table SELECT @Text
	RETURN
END

--沒有=
IF CHARINDEX(@FieldTerminator,@Text)=0
BEGIN
	INSERT @Table SELECT @Text
	RETURN
END

--特殊字元
SET @Text=REPLACE(@Text, ': ',':')
SET @Text=REPLACE(@Text, ' (','(')

--有=
WHILE CHARINDEX(@FieldTerminator,@Text)<>0
BEGIN	
	SET @FieldTerminatorPosition=CHARINDEX(@FieldTerminator,@Text)
	SET @RowTerminatorPosition=CHARINDEX(@RowTerminator,@Text,@FieldTerminatorPosition)
	IF @RowTerminatorPosition=0 SET @RowTerminatorPosition=LEN(@Text)
	SET @Item=RTRIM(LTRIM(SUBSTRING(@Text,1,@FieldTerminatorPosition-1)))
	SET @Value=RTRIM(LTRIM(SUBSTRING(@Text,@FieldTerminatorPosition+1,@RowTerminatorPosition-@FieldTerminatorPosition)))
	IF @RowTerminatorPosition>0 SET @Text=RIGHT(@Text,LEN(@Text)-@RowTerminatorPosition)
	INSERT @Table SELECT @Item+@FieldTerminator+@Value
END

RETURN
END

 

DECLARE @ErrorLogFile NVARCHAR(100)='C:\Users\99050301\Desktop\ERRORLOG'
DEClARE	@Cmd NVARCHAR(MAX)=''

--ErrorLog
IF OBJECT_ID('ErrorLog') IS NOT NULL DROP TABLE ErrorLog
CREATE TABLE ErrorLog (Text VARCHAR(MAX))
SET @Cmd=N'BULK INSERT ErrorLog FROM '+ QUOTENAME(@ErrorLogFile, '''') +' WITH (BATCHSIZE=1000,FIRSTROW=1,ROWTERMINATOR=''\n'',TABLOCK)'
EXEC(@Cmd)

--Deadlock
DECLARE @ErrorLog TABLE (ErrorLogRow INT IDENTITY(1,1), ErrorLogDateTime VARCHAR(50), ErrorLogSpid VARCHAR(50), ErrorLogText VARCHAR(MAX))
INSERT @ErrorLog (ErrorLogDateTime, ErrorLogSpid, ErrorLogText) SELECT LEFT(Text,22), SUBSTRING(Text,23,7), CASE WHEN LEN(Text)>30 THEN LTRIM(RIGHT(Text,LEN(Text)-30)) ELSE Text END FROM ErrorLog 

--DeadlockStmt
DECLARE @ErrorLogRow INT
DECLARE @ErrorLogDateTime VARCHAR(50)
DECLARE @ErrorLogSpid VARCHAR(50)
DECLARE @ErrorLogText VARCHAR(MAX)
DECLARE @ErrorLogStmt TABLE (ErrorLogRow INT, ErrorLogDateTime VARCHAR(50), ErrorLogSpid VARCHAR(50), DeadlockCategory VARCHAR(MAX), ErrorLogStmt VARCHAR(MAX), ErrorLogText VARCHAR(MAX))
DECLARE DeadlockCur CURSOR FOR SELECT ErrorLogRow, ErrorLogDateTime, ErrorLogSpid, ErrorLogText FROM @ErrorLog
OPEN DeadlockCur FETCH NEXT FROM DeadlockCur INTO @ErrorLogRow, @ErrorLogDateTime, @ErrorLogSpid, @ErrorLogText
WHILE @@FETCH_STATUS=0
BEGIN
	INSERT @ErrorLogStmt (ErrorLogRow, ErrorLogDateTime, ErrorLogSpid, DeadlockCategory, ErrorLogStmt, ErrorLogText)
	SELECT @ErrorLogRow, @ErrorLogDateTime, @ErrorLogSpid, '', Stmt, @ErrorLogText 
	FROM [dbo].[ufn_ErrorLogToTable] (@ErrorLogText,'=',' ') 
    FETCH NEXT FROM DeadlockCur INTO @ErrorLogRow, @ErrorLogDateTime, @ErrorLogSpid, @ErrorLogText
END 
CLOSE DeadlockCur;
DEALLOCATE DeadlockCur;

--DeadlockCategory
UPDATE D1 SET DeadlockCategory='deadlock-list: '+D2.ErrorLogDateTime FROM @ErrorLogStmt D1, (SELECT * FROM @ErrorLogStmt WHERE ErrorLogStmt='deadlock-list') D2 WHERE D1.ErrorLogDateTime=D2.ErrorLogDateTime AND D1.ErrorLogRow=D2.ErrorLogRow
UPDATE D1 SET DeadlockCategory=REPLACE(D2.ErrorLogStmt,'process id=','process-list: ') FROM @ErrorLogStmt D1, (SELECT * FROM @ErrorLogStmt WHERE ErrorLogStmt LIKE 'process id%') D2 WHERE D1.ErrorLogDateTime=D2.ErrorLogDateTime AND D1.ErrorLogStmt=D2.ErrorLogStmt
UPDATE D1 SET DeadlockCategory=REPLACE(D2.ErrorLogStmt,'objectname=','resource-list: ') FROM @ErrorLogStmt D1, (SELECT * FROM @ErrorLogStmt WHERE ErrorLogStmt LIKE 'objectname%') D2 WHERE D1.ErrorLogDateTime=D2.ErrorLogDateTime AND D1.ErrorLogStmt=D2.ErrorLogStmt
UPDATE D1 SET DeadlockCategory=REPLACE(D2.ErrorLogStmt,'owner id=','owner-list: ') FROM @ErrorLogStmt D1, (SELECT * FROM @ErrorLogStmt WHERE ErrorLogStmt LIKE 'owner id%') D2 WHERE D1.ErrorLogDateTime=D2.ErrorLogDateTime AND D1.ErrorLogStmt=D2.ErrorLogStmt
UPDATE D1 SET DeadlockCategory=REPLACE(D2.ErrorLogStmt,'waiter id=','waiter-list: ') FROM @ErrorLogStmt D1, (SELECT * FROM @ErrorLogStmt WHERE ErrorLogStmt LIKE 'waiter id%') D2 WHERE D1.ErrorLogDateTime=D2.ErrorLogDateTime AND D1.ErrorLogStmt=D2.ErrorLogStmt

SELECT * FROM @ErrorLogStmt WHERE ErrorLogRow BETWEEN 14 AND 53 ORDER BY ErrorLogRow