sql server 如何在服务运行过程中捕获并记录死锁信息
原文网址（二者内容一样）：
https://bbs.csdn.net/topics/350234619
http://www.imooc.com/article/43592
https://www.cnblogs.com/love828/p/3190258.html
https://www.cnblogs.com/wenBlog/p/5857252.html（后来发现的文章，并没有试验过）
https://www.cnblogs.com/zhijianliutang/p/4113911.html (以上三个链接中的文件路径书写方式均不对，看这个)
测试之后记录自己的使用过程及结果:
1.按以上第二种方法测试，执行代码报错
Procedure sp_trace_create, Line 1.The trace file path is not valid or not supported.//过程sp_trace_create，第1行。跟踪文件路径无效或不受支持
发现文件路径书写错误（我用的是SQL SERVER2008R2和SQL SERVER 2012，都试了），更改为“\”即可
2.生产过程中发生两次死锁，将xml格式数据粘贴出来
<deadlock-list> <deadlock victim="process4992c70"> <process-list> <process id="process4992c70" taskpriority="0" logused="316" waitresource="PAGE: 5:1:239316" waittime="4151" ownerId="231364513" transactionname="INSERT" lasttranstarted="2020-03-31T10:56:16.530" XDES="0x17c97010" lockMode="U" schedulerid="5" kpid="11572" status="suspended" spid="86" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.530" lastbatchcompleted="2020-03-31T10:56:16.527" clientapp="SIMATIC WinCC RT SERVER" hostname="WIN-LI61NNUVF32" hostpid="10712" isolationlevel="read committed (2)" xactid="231364513" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="DNMes.dbo.TRIGGER_T_DD_OP1080" line="23" stmtstart="1652" stmtend="1988" sqlhandle="0x030005008e35f611441fd5003cab00000000000000000000"> update PR_StationStatus set StationStatus = @OK,StationDescribe = @RejectCode ,EditTime = getdate() where RFID = @RFID and Snum = @Snum and StationCode = 'OP1080' </frame> <frame procname="adhoc" line="1" stmtstart="478" sqlhandle="0x02000000604ac30a6c1e3d35be72a799dd6803ed3465d6ad"> INSERT INTO [DNMes].[dbo].[T_DD_OP1080]([OP1080_writeQIS3],[OP1080_writeQIS4],[OP1080_writeQIS5],[OP1080_writeQIS6],[OP1080_writeQIS7],[OP1080_writeQIS8],[OP1080_writeQIS9],[OP1080_writeQIS10],[OP1080_writeQIS11],[OP1080_writeQIS12],[OP1080_writeQIS13],[OP1080_writeQIS1],[OP1080_writeQIS2]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13) </frame> <frame procname="unknown" line="1" sqlhandle="0x0200000092502431a5fe5cdd739b6043e562de33fa121cba"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process49ad8e8" taskpriority="0" logused="316" waitresource="PAGE: 5:1:3746" waittime="4150" ownerId="231364513" transactionname="INSERT" lasttranstarted="2020-03-31T10:56:16.530" XDES="0x547aa3b0" lockMode="U" schedulerid="6" kpid="10840" status="suspended" spid="86" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.530" lastbatchcompleted="2020-03-31T10:56:16.527" clientapp="SIMATIC WinCC RT SERVER" hostname="WIN-LI61NNUVF32" hostpid="10712" isolationlevel="read committed (2)" xactid="231364513" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="DNMes.dbo.TRIGGER_T_DD_OP1080" line="23" stmtstart="1652" stmtend="1988" sqlhandle="0x030005008e35f611441fd5003cab00000000000000000000"> update PR_StationStatus set StationStatus = @OK,StationDescribe = @RejectCode ,EditTime = getdate() where RFID = @RFID and Snum = @Snum and StationCode = 'OP1080' </frame> <frame procname="adhoc" line="1" stmtstart="478" sqlhandle="0x02000000604ac30a6c1e3d35be72a799dd6803ed3465d6ad"> INSERT INTO [DNMes].[dbo].[T_DD_OP1080]([OP1080_writeQIS3],[OP1080_writeQIS4],[OP1080_writeQIS5],[OP1080_writeQIS6],[OP1080_writeQIS7],[OP1080_writeQIS8],[OP1080_writeQIS9],[OP1080_writeQIS10],[OP1080_writeQIS11],[OP1080_writeQIS12],[OP1080_writeQIS13],[OP1080_writeQIS1],[OP1080_writeQIS2]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13) </frame> <frame procname="unknown" line="1" sqlhandle="0x0200000092502431a5fe5cdd739b6043e562de33fa121cba"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process440f8e8" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:22935" waittime="4157" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0x17feee50" lockMode="U" schedulerid="3" kpid="4820" status="suspended" spid="81" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process38bae38" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:251143" waittime="4158" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0xffffffff95af3b50" lockMode="U" schedulerid="1" kpid="10072" status="suspended" spid="81" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process4678c70" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:1037" waittime="4158" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0xffffffffd50e7aa8" lockMode="U" schedulerid="4" kpid="10744" status="suspended" spid="81" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process4405390" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:42278" waittime="4158" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0x1789bdc8" lockMode="U" schedulerid="2" kpid="1132" status="suspended" spid="81" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process4993558" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:257671" waittime="4157" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0x178a7dc8" lockMode="U" schedulerid="5" kpid="10120" status="suspended" spid="81" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process49ad720" taskpriority="0" logused="5980" waitresource="PAGE: 5:1:2279" waittime="4158" ownerId="231364381" transactionname="user_transaction" lasttranstarted="2020-03-31T10:56:15.740" XDES="0x547aa1f8" lockMode="U" schedulerid="6" kpid="11660" status="suspended" spid="81" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process38bb390" taskpriority="0" logused="10000" waittime="4137" schedulerid="1" kpid="6720" status="suspended" spid="86" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-03-31T10:56:16.530" lastbatchcompleted="2020-03-31T10:56:16.527" clientapp="SIMATIC WinCC RT SERVER" hostname="WIN-LI61NNUVF32" hostpid="10712" loginname="sa" isolationlevel="read committed (2)" xactid="231364513" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="DNMes.dbo.TRIGGER_T_DD_OP1080" line="23" stmtstart="1652" stmtend="1988" sqlhandle="0x030005008e35f611441fd5003cab00000000000000000000"> update PR_StationStatus set StationStatus = @OK,StationDescribe = @RejectCode ,EditTime = getdate() where RFID = @RFID and Snum = @Snum and StationCode = 'OP1080' </frame> <frame procname="adhoc" line="1" stmtstart="478" sqlhandle="0x02000000604ac30a6c1e3d35be72a799dd6803ed3465d6ad"> INSERT INTO [DNMes].[dbo].[T_DD_OP1080]([OP1080_writeQIS3],[OP1080_writeQIS4],[OP1080_writeQIS5],[OP1080_writeQIS6],[OP1080_writeQIS7],[OP1080_writeQIS8],[OP1080_writeQIS9],[OP1080_writeQIS10],[OP1080_writeQIS11],[OP1080_writeQIS12],[OP1080_writeQIS13],[OP1080_writeQIS1],[OP1080_writeQIS2]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13) </frame> <frame procname="unknown" line="1" sqlhandle="0x0200000092502431a5fe5cdd739b6043e562de33fa121cba"> unknown </frame> </executionStack> <inputbuf> INSERT INTO [DNMes].[dbo].[T_DD_OP1080] ([OP1080_writeQIS3],[OP1080_writeQIS4],[OP1080_writeQIS5],[OP1080_writeQIS6],[OP1080_writeQIS7],[OP1080_writeQIS8],[OP1080_writeQIS9],[OP1080_writeQIS10],[OP1080_writeQIS11],[OP1080_writeQIS12],[OP1080_writeQIS13],[OP1080_writeQIS1],[OP1080_writeQIS2]) VALUES(N'20090HH5QA000075K2GW 4000 GE',N'0',N'6',N'1',N'1.228935',N'1',N'1.23',N'1',N'0',N'1',N'1.227284',N'1',N'0'); </inputbuf> </process> <process id="process4405558" taskpriority="0" logused="10000" waittime="4159" schedulerid="2" kpid="8496" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-03-31T10:56:16.557" lastbatchcompleted="2020-03-31T10:56:16.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-LI61NNUVF32" hostpid="12320" loginname="sa" isolationlevel="read committed (2)" xactid="231364381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="82" sqlhandle="0x02000000440160099d34ad15cfe69b7cb09ee8af2dd1cda0"> UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@P_0 nvarchar(4000),@P_1 nvarchar(4000))UPDATE [PR_StationStatus] SET [StationStatus]=NULL,[StationDescribe]=NULL,[EditTime]=NULL WHERE ([PR_StationStatus].[Snum] = @P_0 AND [PR_StationStatus].[StationCode] = @P_1 AND [PR_StationStatus].[OffLine] = CAST(0 AS BIT)) </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="239316" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lockffffffff974a1540" mode="UIX" associatedObjectId="72057594401521664"> <owner-list> <owner id="process4405558" mode="UIX"/> </owner-list> <waiter-list> <waiter id="process4992c70" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="3746" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lockffffffff881834c0" mode="UIX" associatedObjectId="72057594401521664"> <owner-list> <owner id="process4405558" mode="UIX"/> </owner-list> <waiter-list> <waiter id="process49ad8e8" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="22935" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lock44872400" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process49ad8e8" mode="U"/> </owner-list> <waiter-list> <waiter id="process440f8e8" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="251143" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lock33922f00" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process38bb390" mode="U"/> <owner id="process38bb390" mode="U"/> </owner-list> <waiter-list> <waiter id="process38bae38" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="1037" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lock29784a00" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process38bb390" mode="U"/> </owner-list> <waiter-list> <waiter id="process4678c70" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="42278" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lockffffffff99d2e400" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process38bb390" mode="U"/> <owner id="process38bb390" mode="U"/> </owner-list> <waiter-list> <waiter id="process4405390" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="257671" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lock6e1266c0" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process49ad8e8" mode="U"/> <owner id="process38bb390" mode="U"/> </owner-list> <waiter-list> <waiter id="process4993558" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="2279" dbid="5" objectname="DNMes.dbo.PR_StationStatus" id="lockffffffffa20bf840" mode="U" associatedObjectId="72057594401521664"> <owner-list> <owner id="process4992c70" mode="U"/> </owner-list> <waiter-list> <waiter id="process49ad720" mode="U" requestType="wait"/> </waiter-list> </pagelock> <exchangeEvent id="Pipeffffffff93fe6340" WaitType="e_waitPipeGetRow" nodeId="3"> <owner-list> <owner id="process4992c70"/> <owner id="process49ad8e8"/> </owner-list> <waiter-list> <waiter id="process38bb390"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe6b2f8340" WaitType="e_waitPipeGetRow" nodeId="3"> <owner-list> <owner id="process440f8e8"/> <owner id="process4993558"/> <owner id="process49ad720"/> <owner id="process4678c70"/> <owner id="process4405390"/> <owner id="process38bae38"/> </owner-list> <waiter-list> <waiter id="process4405558"/> </waiter-list> </exchangeEvent> </resource-list> </deadlock> </deadlock-list>
针对以上文件，如何分析，百度后发现以下说法：
1.victim-list没什么可分析的。
2.process-list中关于各个process的详细信息很重要。
3.再看process中的inputbuf。这个tag表明了process正在运行的语句，因此对于定位死锁非常重要。但这里有一个问题，比如上例中，inputbuf是一个存储过程，其中又嵌套了很多其他的存储过程，而我们需要在其中找出直接导致死锁的语句并优化，从而解决或减少死锁。自此我们已经有的信息是：导致死锁的语句由inputbuf中的语句调用，同时导致死锁的语句必定是对表MatchService的修改语句。如果存储过程很简单，到此DBA已经能够找到直接导致死锁的sql了，分析过程到此结束。而如果存储过程很复杂，则需要进一步分析。
4.现在再进一步考察tag, executionStack。executionStack表明了死锁发生时，由该process调用的，正在运行的所有sql。上例中有4条sql。同时仔细观察上例可以发生，两个process的executionStack是完全相同的，因此考察一个就可以了。另外，如果procname不为空则直接得到了sql，但上例中该tag为空。
我们可能还需要找出包含该sql的具体存储过程，然后进行优化。出了用sql查询外，推荐使用一个叫“SQL Search”的第三方工具，很方便，免费的。
针对以上四点，我个人首先认为是“PR_StationStatus”表发生了死锁（项目中insert into到某表，该表创建了触发器，触发器中有调用update PR_StationStatus表，并且是很多地方都对该表进行update，insert，select等操作，其中update操作最多）。
第二次自定义死锁跟踪代码：
--创建跟踪文件返回值 declare @rc int --创建一个跟踪句柄 declare @TraceID int --创建跟踪文件路径 declare @TraceFilePath nvarchar(500) set @TraceFilePath=N'E:\SQLTest\' --跟踪文件的大小 declare @maxfilesize bigint set @maxfilesize=5 --设置停止的时间 declare @EndTime datetime set @EndTime=null --设置系统默认的操作 declare @options int set @options=2 --设置默认滚动文件的数目 declare @filecount int set @filecount=5 --执行创建跟踪 exec @rc=sp_trace_Create @TraceID output,--跟踪ID @options, @TraceFilePath, @maxfilesize, @EndTime, @filecount if(@rc=0) select @TraceID --查看刚刚创建的跟踪 select * from sys.traces where id=2;--id:跟踪id -- 设置跟踪事件：死锁事件 declare @on bit,@TraceID int set @on = 1 ;set @TraceID = 2; --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns) exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 11, @on exec sp_trace_setevent @TraceID, 148, 4, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 26, @on exec sp_trace_setevent @TraceID, 148, 64, @on exec sp_trace_setevent @TraceID, 148, 1, @on -- 启动跟踪 exec sp_trace_setstatus @TraceID, 1 --查看跟踪文件内容 select * from ::fn_trace_gettable('E:\SQLTest\.trc',1)
过程截图：
等待死锁记录产生....
第三次自定义死锁跟踪
上一次的定义跟踪在过了一周后，发现跟踪文件也没有增加记录，但生产问题一直存在，查询了系统跟踪信息后发现已经没有了。
百度后发现：数据库实例在重启后，自定义跟踪就会取消。
解决方案：创建master库存储过程，并设置为实例启动时自动执行存储过程
USE [master] GO /****** Object: StoredProcedure [dbo].[StartBlackBoxTrace] Script Date: 2020/7/27 17:40:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 2020年7月27日17:26:43 -- Description: 默认重启实例后，自定义跟踪会取消，所以写一个SP做实例启动执行 -- ============================================= CREATE PROCEDURE [dbo].[StartBlackBoxTrace] AS BEGIN SET NOCOUNT ON; --创建跟踪文件返回值 declare @rc int --创建一个跟踪句柄 declare @TraceID int --创建跟踪文件路径 declare @TraceFilePath nvarchar(500) set @TraceFilePath=N'E:\SQLTest\' --跟踪文件的大小 declare @maxfilesize bigint set @maxfilesize=5 --设置停止的时间 declare @EndTime datetime set @EndTime=null --设置系统默认的操作 declare @options int set @options=2 --设置默认滚动文件的数目 declare @filecount int set @filecount=5 --执行创建跟踪 exec @rc=sp_trace_Create @TraceID output,--跟踪ID @options, @TraceFilePath, @maxfilesize, @EndTime, @filecount if(@rc=0) select @TraceID --查看刚刚创建的跟踪 select * from sys.traces where id=2;--id:跟踪id -- 设置跟踪事件：死锁事件 declare @on bit; set @on = 1 ; --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns) exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 11, @on exec sp_trace_setevent @TraceID, 148, 4, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 26, @on exec sp_trace_setevent @TraceID, 148, 64, @on exec sp_trace_setevent @TraceID, 148, 1, @on -- 启动跟踪 exec sp_trace_setstatus @TraceID, 1 END GO EXEC sp_procoption N'[dbo].[StartBlackBoxTrace]', 'startup', '1' GO
--将该存储过程设置为SQL Server服务启动时自动启动 use master EXEC sp_procoption 'StartBlackBoxTrace','STARTUP','ON' print 'ok'
