方法一：SQL2008R2：SELECTXEventData.XEvent.value('@timestamp','datetime2(3)'),cast(XEventData.XEvent.value('(data/value)[1]','varchar(max)')asxml)FROM(SELECTCAST(target_dataASXML)ASTargetDataFROMsys.dm_xe_session_targetsstJOINsys.dm_xe_sessionssONs.address=st.event_session_addressWHERE[name]='system_health'ANDst.target_name=N'ring_buffer')ASDataCROSSAPPLYTargetData.nodes('//RingBufferTarget/event')ASXEventData(XEvent)WHEREXEventData.XEvent.value('@name','varchar(4000)')='xml_deadlock_report';SQL2012：SELECTXEvent.query('(event/data/value/deadlock)[1]')ASDeadlockGraphFROM(SELECTXEvent.query('.')ASXEventFROM(SELECTCAST(target_dataASXML)ASTargetDataFROMsys.dm_xe_session_targetsstJOINsys.dm_xe_sessionssONs.address=st.event_session_addressWHEREs.name='system_health'ANDst.target_name='ring_buffer')ASDataCROSSAPPLYTargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')ASXEventData(XEvent))ASsrc;方法二：--查看是否有阻塞或死锁usemastergodeclare@spidint,@blint,@intTransactionCountOnEntryint,@intRowcountint,@intCountPropertiesint,@intCounterintcreatetable#tmp_lock_who(idintidentity(1,1),spidsmallint,blsmallint)insertinto#tmp_lock_who(spid,bl)select0,blockedfrom(select*fromsysprocesseswhereblocked>0)awherenotexists(select*from(select*fromsysprocesseswhereblocked>0)bwherea.blocked=spid)unionselectspid,blockedfromsysprocesseswhereblocked>0--找到临时表的记录数select@intCountProperties=Count(*),@intCounter=1from#tmp_lock_whoif@intCountProperties=0selectN'现在没有阻塞和死锁信息'asmessage--循环开始while@intCounter<=@intCountPropertiesbegin--取第一条记录select@spid=spid,@bl=blfrom#tmp_lock_whowhereId=@intCounterbeginif@spid=0selectN'引起数据库死锁的是:'+CAST(@blASVARCHAR(10))+N'进程号,其执行的SQL语法如下'elseselectN'进程号SPID：'+CAST(@spidASVARCHAR(10))+N'被'+N'进程号SPID：'+CAST(@blASVARCHAR(10))+N'阻塞,其当前进程执行的SQL语法如下'DBCCINPUTBUFFER(@bl)end--循环指针下移set@intCounter=@intCounter+1enddroptable#tmp_lock_who
