SQLManager running SQL requests, to query the job activity, blocks SQL Agent

Document ID : KB000085885
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
====================================================================================
The blocking effect can take a few seconds, but can impact the global performance of the MSSQL Server when this database activity is important.
====================================================================================

This kind of SQL queries should be executed with the nolock options

Patch level detected:Dollar Universe 6.4.00
Product Version: Dollar.Universe 6.4.01

Description :In case the Activity on the SQL server is high the SQL Manager can block the SQL Agent when querying status of different submitted jobs with the following kind of request.

DECLARE @Date DATETIME
DECLARE @DateInt INT
DECLARE @TimeInt INT
SET @Date = Getdate()
SET @DateInt = CONVERT(VARCHAR(30), @Date, 112)
SET @TimeInt = CAST(REPLACE(CONVERT(VARCHAR(30),@Date, 108), ':', '') as INT)
SELECT job_id
FROM msdb.dbo.sysjobactivity
WHERE session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND job_id NOT in (SELECT job_id FROM msdb.dbo.sysjobhistory WHERE run_date >= @DateInt AND run_time >= @TimeInt)
AND start_execution_date IS NOT null
AND stop_execution_date IS null AND job_id IN ('D03D161E-2431-4474-92C3-222A3494056B','F4C0F5A2-E8D6-428B-8C26-7C2B96E78152','C0E0C574-81A5-472B-AA8C-919DCA418B4A','46CA6FB1-6C76-4159-9FFC-4C7D55539550','F378C0BC-B05B-46FA-9CD3-C6788AC93BBA','70AF792D-C246-485F-ABF7-40051D071374','EAB2A1FE-5E5C-40FE-A324-F99F32E09719','26FCBADC-F627-49CD-9E89-10EDE19CC80C','A5DB6604-8EFA-4107-825E-9486B06D6B5D','EA80CFEB-E06B-491C-B0E0-ADB64B95A430','B7CB19DE-1E0A-4C62-AA88-1B150694DEEA','341A19DA-5843-4BF5-A6D3-47F93791080D','2ADEADA4-076F-498E-866F-3A983103CA92','325476D8-934C-47A4-9965-E1B7CA9D663A','1774CA4F-BF4E-4F17-8609-962B617C9A16','1857B02D-0179-4114-AC4C-5FB33DAE13A5','1F422C86-4DCF-44CC-882F-0EA8B0579872','ECA701EA-444B-43B6-A938-DF0A2BF5D48F','5728014D-91ED-49F2-9D48-490C9A29A509','41F3EE94-AA32-4CD2-8C2C-9DF032B386E4','47EEDF11-0914-4509-8981-92619106B07C','34BC29FD-A451-4EFB-A259-883231E0C0A1','D7CD6EB9-E94F-4BA2-AA83-5E67BE99C8DC','FB1B1BD8-4E4F-4EED-81C5-14066887586A','9E081BC4-85C8-4528-8D89-2D309ED2601B','3AC1DBA4-9EB2-44A1-95C5-211E666553C8','329B6524-4860-4211-B71F-D03CE1F08729','FC024C1F-0E27-4826-A78A-5EA0378B3229','4ECFD8B6-4862-4511-8DF6-A7B49CBAE4EE','A9931F72-C094-4B83-973A-F6EC0E9E5961')
Environment:
OS: Windows Server 2008
OS Version: Windows 2012
Cause:
Cause type:
Defect
Root Cause: N/A
Resolution:
So will allow the user to activate or deactivate the nolog options in the node settings on the DUAS Node.

Fix Status: Released

Fix Version(s):
Component: Application.Server
Version: Dollar.Universe 6.4.42
Additional Information:
Workaround :
N/A