check deadlocking info in SQL server

 To get deadlocking info in log , one of my practice is:

I enabled the trace flag

  DBCC TRACEON(1204, -1) 

DBCC TRACEON(1222, -1) 


----select deadlocks

SELECT

   xed.value('@timestamp', 'datetime2(3)') as CreationDate,

   cast(xed.query('.').value('(/event/data/value/text())[1]', 'nvarchar(MAX)') AS xml) AS XEvent

FROM

(

   SELECT CAST([target_data] AS XML) AS TargetData

   FROM sys.dm_xe_session_targets AS st

      INNER JOIN sys.dm_xe_sessions AS s

         ON s.address = st.event_session_address

      WHERE s.name = N'system_health'

         AND st.target_name = N'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)

ORDER BY CreationDate DESC


also uses Amit's method https://troubleshootingsql.com/2012/09/06/system-health-session-and-deadlocks/ 




Comments

Post a Comment

Popular posts from this blog

Sysaux tablespace is too big

Image
 today I received my report alert regarding one of Oracle database tablespace SYSAUX is over 80% (I created automated job using SSIS package to check tablespace usage daily) following steps to resolve it:  /*  2021.07.08  Documentation steps for troubleshooting Sysaux tablespace is huge --85% full  */   ---1. see what uses the most select occupant_name,occupant_desc, space_usage_kbytes/1024 MB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc; ---find it is SM/Advisor Server Manageability - Advisor Framework 25GB --now look for top 10 big objects   select * from (                select bytes/1024/1024 MB, blocks, s.SEGMENT_NAME, s.partition_name, s.segment_type, s.tablespace_name                  from dba_segments s                 where owner='SYS'             ...
Read more

PowerBI license

 1. on premise license covers by enterprise server with SA 2. Office 365 E5 license covers powerbi Pro 10GB limit, data can be refreshed max 8 times a day 3. free version is for single user 10GB limit
Read more

SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future

  SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future when I change sql agent service AD user password to meeting security requirement, I used the below powershell script and change all my 200+ sql instances sql agent service, no service restart required. Today just found after I done a patching on one of my clusters, the sql agent service didn't start, I thought the password was old, and retype pwd, still failed to start, then I tried to reload the AD user by search user name in AD and type the password, it works. script : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null $text=Get-Content C:\sql\serverlist0.txt $TotalComputers=$text.Length write-host "the total number of computers is $TotalComputers" For ($i=0; $i -lt $text.Length; $i++) {     # write-host " the server name is $text[$i] being working on "     $SMOWmiserver = New-Object (‘Microsoft.SqlServ...
Read more