Wednesday, February 24, 2021

Drive space via powershell

If you want to get info for a remote server, you need to call the following.  This will enter a remote powershell session on the remote host.

 Enter-PSSession <hostname>

The following command will return the used and free space for the C: and Z: drives.

get-psdrive <drive> | ? {$_.Name -eq "Z" -or $_.Name -eq "C"} |  % {"Free(GB) " + $_.Free/1GB + " Used(GB) " + $_.Used/1GB]




As you see this command calls get-psdrive which gets the info for all drives.  You can optionally pass in which drive you want to get info for.  In this case it returns all drives so we can then select the two drives we are interested in.  The last step formats the output for each object in the pipeline.






Tuesday, February 23, 2021

How to view the XML deadlock graph

An easy way to view deadlock information is to find the deadlock report in the Extended Event system_health session.  Under the system_health session, double-click on package0.event_file.  In the window that opens, create a filter so that you only see the xml_deadlock_report.


Depending on the number of rows that you have recorded this may take awhile.  Once you have your final list, you can double-click on the report that you want to examine.  The report will come up in XML and have tons of good information for you to examine.  

However you sometimes want to view the deadlock graph itself.  To do this save the xml but change the file extension to ".xdl".  Now if you drag that file to SQL Server Management Studio, the graph will be displayed.

Similarly you can query the ring buffer with:

WITH SystemHealth
AS ( 
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
       JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
       AND st.target_name = 'ring_buffer')
SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph
FROM SystemHealth
       CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
 

Or the extended event file with:

Start by finding the path to the files:
SELECT target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s 
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND target_name = 'event_file'


then query the data
Select Cast(event_data AS xml) AS TargetData
FROM sys.fn_xe_file_target_read_file('C:\events\system_health*.xel',NULL,NULL,NULL)
WHERE object_name like 'xml_deadlock_report';