SQL AGENT SSIS JOBS IN HUNG STATE

SQL SERVER VERSION:Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64)
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
OS version - WINDOWS SERVER 2012 R2 STANDARD

SSIS - SQL 2014 - Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725)

Last few months we are facing the issues 2 -3 times

We have observed that Jobs which are scheduled using T-SQL are getting completed successfully, issue is with SQL jobs which are configured by SSIS and those are getting halted after some time post re-starting the Integration service.

The SSIS Jobs will works for while and start to get hung and wont do anything -Anyone can tell me what could be the issue Why only the SSIS jobs hung and others are works fine?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Dec 04, 2018 at 11:43 AM

What do you see in the sql agent or ssis logs when these jobs hang?

When you say “hang” do you mean that the jobs start then remain in the running state but never complete?

0 ·

jagadeesh KenJ commented · Dec 04, 2018 at 12:09 PM

No errors, it just keep on executing

if it throws any error we can find the root cause

Nothing is logged in event log, sql logs, agent logs

No Blocks or locks

-Works fine when running via SSDT

This is only one I can spot

0 ·

vvk7s.png (40.4 KiB)

KenJ jagadeesh commented · Dec 05, 2018 at 12:16 AM

Do you store the packages in the ssis catalogue? If so, you could be able to check execution logs there to see if the package is running any of its tasks.

If not, and it doesn’t log any errors, we’re in a tough spot. You might be able to add incorporate logging into the package to help.

0 ·