Execute the following query. If results are returned, check the BLOCKING_SESSION_ID column to see whether any of the returned queries contain a NULL or 0 for this column. If so, refresh the query several times. Check both the Session_ID and Blocking_Session_ID columns to see whether this same blocking query remains for several minutes. If the Session_ID does not change on the query that shows a NULL or 0 under the Blocking_Session_ID column, this is an indication of a blocking query.
SELECT ER.SESSION_ID, HOST_NAME, PROGRAM_NAME, ORIGINAL_LOGIN_NAME, ER.READS, ER.WRITES, ER.CPU_TIME, WAIT_TYPE, WAIT_TIME, WAIT_RESOURCE, BLOCKING_SESSION_ID
FROM SYS.DM_EXEC_SESSIONS ES
LEFT JOIN SYS.DM_EXEC_REQUESTS ER ON ER.SESSION_ID = ES.SESSION_ID
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(ER.SQL_HANDLE) ST
WHERE BLOCKING_SESSION_ID > 0
UNION
SELECT ES.SESSION_ID, HOST_NAME, PROGRAM_NAME, ORIGINAL_LOGIN_NAME, ES.READS, ER.WRITES, ER.CPU_TIME, WAIT_TYPE, WAIT_TIME, WAIT_RESOURCE, BLOCKING_SESSION_ID
FROM SYS.DM_EXEC_SESSIONS ES
LEFT JOIN SYS.DM_EXEC_REQUESTS ER ON ER.SESSION_ID = ES.SESSION_ID
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(ER.SQL_HANDLE) ST
WHERE ES.SESSION_ID IN (SELECT BLOCKING_SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WHERE BLOCKING_SESSION_ID > 0) ORDER BY BLOCKING_SESSION_ID
Workaround for blocking sessions:
- Stop all Agent Handler services.
- Stop the ePO services.
- Open SQL Server Management Studio.
- Connect to the ePO database.
- Execute the following SQL statement to determine the total rows:
SELECT COUNT(1) FROM SCOR_SA_REQUEST WHERE APPROVAL_STATUS = 1
- Change the APPROVAL_STATUS value from 1 to 5 to work around the Auto Approval logic bug:
UPDATE SCOR_SA_REQUEST SET APPROVAL_STATUS = 5 WHERRE APPROVAL_STATUS = 1
- Start all Agent Handler services.
- Start the ePO services.
- Use the Create Custom Policy option and select Global Rules to apply Policy Discovery rules instead of Approve Globally.
