SQLTeam.com | Weblogs | Forums

SSIS SQL Agent job shows running after server hard shutdown

sql2014

#1

I was running an SSIS package SQL Agent job when the virtual server was stopped hard. Now the Report > All executions shows that job as still running, even though it is not. I believe that the job was not able to update the status to "Unexpected termination" before it died so there is a flag or a record missing from a table in the SSIS DB.

Is there a way to tell SQL Server SSIS Reports that the job is not running? Some record I can write, flag I can set, Etc?

Many Thanks,
^Herkimer


#2

Can you just run the job again and maybe it'll reset things?


#3

Dunno about SSIS, but SQL Agent does this too. It is possible, looking at the History for SQL Agent to think that a job is (still!) running several times.

I have seen code online that works around this limitation.

My only other thought is that is an on-startup event in SQL, and you could use that to "set" any "Still Running" BITs to "failed" ... but I, personally, would be reluctant to do that on any System tables.


#4

Restarting the job will run since it is not already running but it does not change the reporting output. It still sees it as running. There is a final status record that the "Reports > All Executions" looks for to show end time and calculate the Duration. That instance still lists as running and will most likely overflow the duration field max value soon.


#5

Kristen,

I did hear somewhere that there is a series of steps to run to force a ghost process to be put to rest. :wink:
I was hoping someone on this site would know it.

I believe it involves stopping either SQL Agent or SQL Service and then massaging the SSIS data.

^Herkimer