失眠网 > sql 查询超时已过期_监视来自SQL Server代理作业的查询超时过期消息

sql 查询超时已过期_监视来自SQL Server代理作业的查询超时过期消息

时间：2022-11-23 10:21:39

sql 查询超时已过期

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

SQL Server为您提供了一个很好的解决方案，可以使用SQL Server代理作业自动执行许多管理任务。这些作业由Windows服务从操作系统端处理，该Windows服务负责执行这些作业并向SQL Server系统表提供有关这些作业的元数据。 SQL Server代理用于作业管理的系统数据库称为msdb数据库。与作业步骤，时间表和历史记录有关的所有信息都可以在msdb数据库表中找到。 msdb系统数据库还负责SQL Server邮件，Service Broker，SQL Server维护计划和数据库备份历史记录。

The msdb database tables that store information about the SQL Server Agent jobs are: the dbo.sysjobactivity which contains information about the SQL Server Agent job activates, the dbo.sysjobhistory table that keeps information about the execution history for the agent jobs , the dbo.sysjobs table that contains information about all the SQL Agent jobs, the dbo.sysjobschedules table that stores information about the schedules for all SQL Agent job, the dbo.sysjobservers table that contains the association of the SQL Agent jobs with the target servers, the dbo.sysjobsteps table that stores information about the SQL Agent jobs steps and the dbo.sysjobstepslogs table that stores logs about the SQL Agent jobs steps.

存储有关SQL Server代理作业信息的msdb数据库表包括：dbo.sysjobactivity，其中包含有关SQL Server Agent作业已激活的信息； dbo.sysjobhistory表，其中包含有关代理作业的执行历史的信息； dbo。 sysjobs表（包含有关所有SQL Agent作业的信息）， dbo.sysjobschedules表（存储有关所有SQL Agent作业的日程表的信息）， dbo.sysjobservers表（包含SQL Agent作业与目标服务器的关联）， dbo .sysjobsteps表用于存储有关SQL Agent作业步骤的信息，而dbo.sysjobstepslogs表用于存储有关SQL Agent作业步骤的日志。

There are three main database fixed roles in the msdb database that control the access to the SQL Server Agent. The first role isSQLAgentUserRolethat is least privileged one in the msdb database. This role members have access on the local jobs and local job schedules that they owned only. This role members can see only the Jobs node from the SQL Server Agent.

msdb数据库中有三个主要的数据库固定角色，用于控制对SQL Server代理的访问。第一个角色是SQLAgentUserRole，它在msdb数据库中拥有最少的特权。该角色成员有权访问他们仅拥有的本地作业和本地作业计划。该角色成员只能从SQL Server代理看到Jobs节点。

The second role isSQLAgentReaderRole, that members have access to see all the jobs and job schedules they owned and the ones they don’t own. But they can’t change on the jobs and schedules that they don’t own. This role members can see only the Jobs node from the SQL Server Agent.

第二个角色是SQLAgentReaderRole，成员有权查看他们拥有的所有作业和作业计划以及不拥有的作业。但是他们无法更改自己不拥有的工作和时间表。该角色成员只能从SQL Server代理看到Jobs节点。

The last role isSQLAgentOperatorRole, which is the most privileged role in the msdb database. The members of this role can start and stop the execution of the local jobs, enable and disable it, and they have the ability to delete the jobs history for these local jobs. But they are not able to change in the jobs that they don’t own. This role members can see all the SQL Server Agent nodex except the Error Logs one.

最后一个角色是SQLAgentOperatorRole，它是msdb数据库中最特权的角色。该角色的成员可以启动和停止本地作业的执行，启用和禁用本地作业，并且可以删除这些本地作业的作业历史记录。但是他们无法改变他们不拥有的工作。该角色成员可以看到除错误日志之一以外的所有SQL Server代理节点。

我们如何才能在步骤级别上获得正确的工作结果？ ( How could we get the correct job result on the step level? )

One of the DBA’s daily tasks is checking the SQL Server Agent jobs that are scheduled at night and report back any failure to the corresponding system owners. A system owner reported to me that her scheduled job failed last night even though the evidence indicates that it was completed successfully. To get to the bottom of this I went through the job steps, and I found out that the job contains remote query that is using linked server and it gave Query Timeout Expired error.

DBA的日常任务之一是检查晚上安排SQL Server代理作业，并将任何故障报告给相应的系统所有者。一位系统所有者向我报告说，她的预定工作昨晚失败了，即使有证据表明该工作已成功完成。要深入了解这一点，我完成了工作步骤，发现该工作包含使用链接服务器的远程查询，并且出现了“查询超时过期”错误。

Doing the below steps, I was under the impression that the system owner’s job was completed successfully, even though at later stage I found out the opposite.

执行以下步骤，给我的印象是，系统所有者的工作已成功完成，尽管在稍后阶段我发现相反的情况。

The result of the SQL server agent jobs can be checked by drilling down the job to View History as below:

可以通过按以下方式将作业向下钻取至“查看历史记录”来检查SQL Server代理作业的结果：

The job history window will be displayed showing valuable information about the last few runs of the chosen scheduled job including Date, Server, Job Name, Result Message and the Duration in seconds:

将显示作业历史记录窗口，其中显示了有关所选计划作业的最后几次运行的重要信息，包括日期，服务器，作业名称，结果消息和以秒为单位的持续时间：

As shown in the snapshot above, the last run of our job succeeded. But the system’s owner complained that the job failed?

如上面的快照所示，上一次工作成功完成。但是系统的所有者抱怨工作失败了吗？

Let’s expand the job log to show the result of each step as below:

让我们展开作业日志以显示每个步骤的结果，如下所示：

The step’s result still misleading us by showing the green tick sign beside the step, showing that the step also completed successfully. Going through the step result message carefully, we are shocked with this message: “Query timeout expired". [SQLSTATE 01000] (Message 7412).The step succeeded.”.This means that the query duration exceeded the server’s remote query timeout duration, raising this message from the SQL linked server OLE DB provider.

通过在步骤旁边显示绿色的勾号，该步骤的结果仍然会误导我们，表明该步骤也已成功完成。仔细查看步骤结果消息，我们对以下消息感到震惊：“查询超时到期”。[SQLSTATE 01000]（消息7412）。步骤成功。”。这意味着查询持续时间超过了服务器的远程查询超时持续时间，从SQL链接服务器OLE DB提供程序引发此消息。

SQL Server remote query is a query that contains outgoing connection to a remote database. A SQL server parameter calledremote query timeoutis used to decide how long a remote query will take before initiating timeout message. The default remote query timeout value is 600 seconds. Setting the value to 0 will disable the timeout, so the query will wait until it is canceled.

SQL Server远程查询是一个包含到远程数据库的传出连接的查询。一个称为远程查询超时的SQL Server参数用于确定启动超时消息之前远程查询将花费多长时间。远程查询的默认超时值为600秒。将该值设置为0将禁用超时，因此查询将等待直到被取消。

If there is a network latency between you server and the remote SQL server, you can change this value to resolve the query timeout issue from the Connections tab of the Server Properties dialog box below:

如果服务器和远程SQL Server之间存在网络延迟，则可以从下面的“服务器属性”对话框的“连接”选项卡中更改此值以解决查询超时问题：

Our main concern here, how we could have an automated way to trace the query timeout message within the SQL Agent job steps result in our case, or any failure message in your case, in order to get a better indication of the job status.

这里我们主要关心的问题是，如何在我们的情况下自动跟踪SQL Agent作业步骤中的查询超时消息，或者在您的情况下跟踪失败消息，以便更好地指示作业状态。

Let’s try first to view the job steps result that contains query timeout message, which is stored in the sysjobhistory and sysjobs tables from the msdb system database:

让我们首先尝试查看包含查询超时消息的作业步骤结果，该消息存储在msdb系统数据库的sysjobhistory和sysjobs表中：

USE msdbGo SELECT JS.name AS JobName,JH.step_name AS StepName,JH.message AS StepMessage, JH.run_duration AS StepDuration, JH.run_date AS TSFROM sysjobhistory JH INNER JOIN sysjobs JSON JS.job_id = JH.job_idWHERE JH.message LIKE '%Query timeout expired%'ORDER BYJH.run_date descGO

Executing the query, the result will be as follows:

执行查询，结果如下：

To be initiative and to avoid future complains related to the same problem, it is better to automate this process and review it as a part of your daily check. To achieve that, we will create a table that will host the query results, modify the previous script to fill that table with the previous day’s jobs result.

为了主动起见并避免以后再提出与同一问题有关的投诉，最好使此过程自动化并将其作为日常检查的一部分进行检查。为此，我们将创建一个表来托管查询结果，修改前一个脚本以将前一天的工作结果填充到该表中。

Let’s start with the table creation using the simple T-SQL script below:

让我们开始使用下面的简单T-SQL脚本创建表：

USE [SQLShackDemo]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[JobStepsHistory]([JobName] [nvarchar](50) NULL,[StepName] [nvarchar](50) NULL,[StepMessage] [nvarchar](max) NULL,[StepDuration] [int] NULL,[TS] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

Once the table is ready, we will modify our script to insert into that table, any query timeout message during the last day. It is better to schedule the below script as a SQL Agent job that will be run the first thing in the morning, to make sure that all the scheduled jobs are finished:

表准备好后，我们将修改脚本以将最后一天中的任何查询超时消息插入该表。最好将下面的脚本作为SQL Agent作业进行调度，该脚本将在第一天早上运行，以确保所有调度的作业均已完成：

USE [SQLShackDemo]GOINSERT INTO DBO.JobStepsHistorySELECT JS.name, JH.step_name,JH.message, JH.run_duration, JH.run_dateFROM MSDB.DBO.sysjobhistory JH INNER JOIN MSDB.DBO.sysjobs JS ON JS.job_id = JH.job_idWHERE JH.message LIKE '%Query timeout expired%'AND datepart (day,CONVERT(CHAR(10), CAST(STR(JH.run_date,8, 0) AS dateTIME), 111))= datepart(day,getdate()-1)ORDER BYJH.run_date descGO

What is required from your side now, is a simple select statement from that table, every morning, to make sure that no query timeout occurred last night on your scheduled jobs. Also you can have more advanced automated way to send you the content of that table by email. You can also modify the previous script to trace any type of misleading messages you may face by replacing the query timeout message in LIKE statement inside WHERE clause.

您现在需要的是每天早晨从该表中进行选择的简单语句，以确保昨晚对计划的作业没有查询超时发生。您还可以采用更高级的自动方式，通过电子邮件向您发送该表的内容。您还可以通过替换WHERE子句中LIKE语句中的查询超时消息来修改以前的脚本以跟踪您可能会遇到的任何类型的误导性消息。

You can simulate a test scenario for the “Query Timeout Expired” problem by creating a SQL stored procedure in the remote database, including WAITFOR delay exceeding the default 10 minutes.

您可以通过在远程数据库中创建SQL存储过程（包括超过默认10分钟的WAITFOR延迟）来模拟针对“查询超时已过期”问题的测试方案。