WMI Provider for Server Events - Professional SQL Server 2008 Administration with Windows PowerShell - page 227

$query = "SELECT * FROM DEADLOCK_GRAPH"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections = "LoginSid", "LoginName", "SQLInstance", "IsSystem", "PostTime", `
"ComputerName", "SessionLoginName", "SPID", "StartTime", "TransactionID", `
"EventSequence", "TextData"
Get-WMIEvent $query $sqlnamespace $selections
Run the script in a Windows PowerShell console:
. C:\DBAScripts\dbaLib.ps1
C:\DBAScripts\MonitorDeadlocks.ps1
In order to test this script, run the following query in a SSMS query window:
CREATE TABLE Test (i int)
INSERT Test SELECT 1
GO
BEGIN TRAN
UPDATE Test SET i = 1
WAITFOR DELAY '00:00:30'
UPDATE Test2 SET i = 1
WAITFOR DELAY '00:02:00'
COMMIT
DROP TABLE Test
This session holds exclusive locks on the Test table, and tries to update the Test2 table.
Within 30 seconds, in another SSMS query window, run this query:
CREATE TABLE Test2 (i int)
INSERT Test2 SELECT 1
GO
BEGIN TRAN
UPDATE Test2 SET i = 1
WAITFOR DELAY '00:00:30'
UPDATE Test SET i = 1
WAITFOR DELAY '00:02:00'
COMMIT
DROP TABLE Test2
This session holds exclusive locks on the Test2 table, and tries to update the Test table. You have a
deadlock situation here because these two sessions are blocking each other, trying to update the table
the other process is holding. Our script, MonitorDeadlocks.ps1 , detects the deadlock and prints out the
deadlock graph.
Figure 10-2 shows the output. The last property, TextData , cannot be shown in full.