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

are generated. For example, if you want a blocked process report to be generated for each session that is
blocked for 30 seconds, run the following query on the SQL Server:
Exec sp_configure 'show advanced options', 1
RECONFIGURE
GO
Exec sp_configure 'blocked process threshold', 30
RECONFIGURE
GO
The setting change becomes effective immediately without a server stop and restart. Please note that
each report contains only two connections of a blocking. Unlike the DEADLOCK_GRAPH event, which shows
a deadlock chain, this event class does not show the complete chain. You have to work through all the
reports gathered at the same time to figure out which process is at the head of the chain.
The MonitorBlockings.ps1 script defines the event query, the namespace for the default instance, and
the properties of blocked process reports:
$query = "SELECT * FROM BLOCKED_PROCESS_REPORT"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections = "LoginSid","PostTime","SQLInstance","IsSystem",`
"DatabaseID","ComputerName","SessionLoginName","SPID", `
"TransactionID","EventSequence","IndexID","ObjectID", "TextData", `
"EndTime","Duration","Mode"
Get-WMIEvent $query $sqlnamespace $selections
Run the script in a Windows PowerShell session:
. C:\DBAScripts\dbaLib.ps1
C:\DBAScripts\MonitorBlockings.ps1
While the scripts are running, open a query window in SSMS and run this query:
USE AdventureWorks2008
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]')
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:01:00'
COMMIT
This session holds exclusive locks for one minute on the Test table.