SQL Server 作业活动(作业失败，但作业活动中的历史记录仍显示“正在运行"状态) [英] SQL Server job activity (job failed but history records in job activity still showing 'running' status )

问题描述

我正在查看一份 SSIS 作业执行报告，其中显示了以下报告:

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

最近一次成功了，但是当你查看 ID:217583 时，它仍在运行并且从未完成(持续时间不断增加)，当我在 sql server agent 中检查作业活动时，该执行应该在之前失败，我之所以这么说，是因为开始时间匹配.这是 sql server 代理中的作业历史记录:

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.

有人有什么想法吗?我尝试了 EXEC msdb..sp.stop_jobs 命令，但找不到该作业 ID.

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?

提前谢谢:)

推荐答案

如果您正在执行此包作为 SSISDB 中的作业，您可以使用如下停止操作过程.

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 这里是停止操作的参考.如果此链接中断，...

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

SSISDB 数据库将执行历史存储在用户不可见的内部表中.但是，它通过您可以查询的公共视图公开您需要的信息.它还提供了存储过程，您可以调用这些存储过程来执行与包相关的常见任务.

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.

通常，您在 SQL Server Management Studio 中管理服务器上的 Integration Services 对象.但是，您也可以查询数据库视图并直接调用存储过程，或者编写调用托管 API 的自定义代码.SQL Server Management Studio 和托管 API 查询视图并调用存储过程来执行它们的许多任务.例如，您可以查看当前在服务器上运行的 Integration Services 包列表，并在必要时请求停止包.

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.

查看运行包列表

您可以在活动操作"对话框中查看当前在服务器上运行的程序包列表.有关详细信息，请参阅活动操作对话框.有关可用于查看正在运行的包列表的其他方法的信息，请参阅以下主题.

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 访问

要查看服务器上正在运行的包列表，请查询视图、catalog.executions(SSISDB 数据库)以获取状态为 2 的包.通过托管 API 进行编程访问请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类.

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 访问

要停止在服务器上运行的程序包，请调用存储过程 catalog.stop_operation(SSISDB 数据库).通过托管 API 进行编程访问请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类.

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.

查看已运行包的历史

要查看在 Management Studio 中运行的包的历史记录，请使用所有执行"报告.有关所有执行报告和其他标准报告的详细信息，请参阅 Integration Services 服务器的报告.有关可用于查看运行包历史记录的其他方法的信息，请参阅以下主题.

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 访问

要查看有关已运行的包的信息，请查询视图 catalog.executions(SSISDB 数据库).通过托管 API 进行编程访问请参阅 Microsoft.SqlServer.Management.IntegrationServices 命名空间及其类.

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.

这篇关于SQL Server 作业活动(作业失败，但作业活动中的历史记录仍显示“正在运行"状态)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！