SQL Server job activity (job failed but history re

Question

I am checking one SSIS job execution report, which shows me the below report:

The most recent one succeeded, but when you take a look at ID:217583, it is still running and never finished (duration keeps increasing), and when I check the job activity in sql server agent, that execution should failed before, the reason why I said that was because the start time matched. Here is the job history in sql server agent :

So I assume this job execution failed but for some mystery reason, it still shows (or running) in the background with 'running ' status.

Does anybody have any ideas? I tried to EXEC msdb..sp.stop_jobscommand, but cannot locate that job ID.

Can anybody tell me what was really happened? Is this job still running somewhere else? If so, how to locate that job execution and stop it? Or how to let the report does not show this weird record anymore?

Thx in advance :)

Answer 1

If your are executing this package as a job from the SSISDB, you can use the stop operation procedure as follows.

USE SSISDB
GO

EXEC [catalog].[stop_operation] 217583

https://msdn.microsoft.com/en-us/library/hh213131.aspx here is a reference to stopping operations. In case this link breaks, ...

The SSISDB database stores execution history in internal tables that are not visible to users. However it exposes the information that you need through public views that you can query. It also provides stored procedures that you can call to perform common tasks related to packages.

Typically you manage Integration Services objects on the server in SQL Server Management Studio. However you can also query the database views and call the stored procedures directly, or write custom code that calls the managed API. SQL Server Management Studio and the managed API query the views and call the stored procedures to perform many of their tasks. For example, you can view the list of Integration Services packages that are currently running on the server, and request packages to stop if you have to.

Viewing the List of Running Packages

You can view the list of packages that are currently running on the server in the Active Operations dialog box. For more information, see Active Operations Dialog Box. For information about the other methods that you can use to view the list of running packages, see the following topics.

Transact-SQL access

To view the list of packages that are running on the server, query the view, catalog.executions (SSISDB Database) for packages that have a status of 2. Programmatic access through the managed API See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.

Stopping a Running Package You can request a running package to stop in the Active Operations dialog box. For more information, see Active Operations Dialog Box. For information about the other methods that you can use to stop a running package, see the following topics.

Transact-SQL access

To stop a package that is running on the server, call the stored procedure, catalog.stop_operation (SSISDB Database). Programmatic access through the managed API See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.

Viewing the History of Packages That Have Run

To view the history of packages that have run in Management Studio, use the All Executions report. For more information on the All Executions report and other standard reports, see Reports for the Integration Services Server. For information about the other methods that you can use to view the history of running packages, see the following topics.

Transact-SQL access

To view information about packages that have run, query the view, catalog.executions (SSISDB Database). Programmatic access through the managed API See the Microsoft.SqlServer.Management.IntegrationServices namespace and its classes.