Monitoring SQL Server - Professional SQL Server 2008 Administration with Windows PowerShell - page 444

AND type in (N'U'))
DROP TABLE [dbo].[Test]
GO

CREATE TABLE Test (i int)

INSERT Test SELECT 1
GO
BEGIN TRAN
UPDATE Test SET i = 1
WAITFOR DELAY '00:10:00'
COMMIT

This session holds exclusive locks for 10 minutes on the Test table.

In another query window in SSMS, run this query:

USE AdventureWorks2008
UPDATE Test SET i = 1

As shown in Figure 18-14, the blocking is picked up by Monitor-Blocking.ps1 and an alert e-mail is sent
to [e-mail] with detailed information about the blocking.

Monitoring Deadlocks
To monitor deadlocks, we just follow the preceding approach and create a new monitoring script,
Monitor-Deadlock.ps1 , and an installation script to install the script as a scheduled task on each SQL
Server host. In the new blocking monitoring script, we only need to change the parameter values of the
Notify-WMIEvent function to monitor blockings. The DEADLOCK GRAPH event class contains the deadlock
events; therefore, the event query parameter is as follows:

$query = "SELECT * FROM DEADLOCK_GRAPH"

Like the previous monitoring scripts, our new deadlock monitoring script, Monitor-Deadlock.ps1 , will
accept the SQL Server instance name as a parameter, and use the instance name to determine the names-
pace, root instance name. The Monitor-Deadlock.ps1 script can be

used to monitor multiple instances on the same host:

\ Microsoft \ SqlServer \ ServerEvents \

C:\DBAScripts\Monitor-Deadlock.ps1 $instanceName

Out of all the properties of the DEADLOCK GRAPH event class, the computer name, SQL Server instance
name, and start time provide when and where the event occurs. The TextData property provides details
about each process that participated in the deadlock:

$selections = "ComputerName", "SQLInstance", "StartTime", "TextData"