SQLManager running SQL requests, to query the job activity, blocks SQL Agent
Article ID: 85885
Updated On:
Products
CA Automic Dollar Universe
Issue/Introduction
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')
====================================================================================
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
OS Version: Windows 2012
Cause
Cause type:
Defect
Root Cause: N/A
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
Fix Status: Released
Fix Version(s):
Component: Application.Server
Version: Dollar.Universe 6.4.42
Additional Information
Workaround :
N/A
N/A
Feedback
Yes
No
Powered by
