SQL Scripts to find all the Blocked Process/Query in a Power Monitoring Expert Database

Issue
When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes
request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block.
Although in SQL Server a blocked process usually resolves itself when the first process releases the resource, there are times when a process
holds a transaction lock and doesn't release it.

Product Line
ION EEM
ION Enterprise 6.0. x

Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.2
Power Monitoring Expert 8.2
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023
Power Monitoring Expert 2024

Environment
SQL Server 2005

SQL Server 2008 R 2

SQL Server 2012

SQL Server 2014

SQL Server 2016

SQL Server 2017

SQL Server 2018

SQL Server 2019

Cause
Blocking occurs when one or more sessions request a lock on a resource, such as a row, page or table, but SQL Server cannot grant that lock because
another session already holds a non-compatible lock on that resource.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process.

To determine which process is the blocking process log into SQL Server Management Studio and run the attached SQL script (Blocked_Process.sql).
Alternatively, execute the query(s) below :

Script to find all blocked processes:

SELECT
spid,
status,
loginame=SUBSTRING(loginame,1,12),
hostname=SUBSTRING(hostname,1, 12),
blk = CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid),1, 10),
cmd,
waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

Script to identify the blocking query:

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Script to view all current processes / sessions on the server:

select * from master.dbo.sysprocesses

To kill a process use the task manager to "End Task" on the process relative to the SQL script output

• to open the task manager you can use CTRL+SHIFT+ESC

發佈於: 施耐德電機Taiwan