SQLServer作业监控-CSDN博客

本文介绍了一种使用PowerShell脚本监控SQLServer代理作业状态的方法，包括收集所有受监控实例的上次运行信息，以及如何设置和运行脚本。

作者： Alejandro Cobar | 更新日期：2020-07-27 | 评论 | 相关： 1 | 2 | 3 | 4 | 5 | 6 | 更多 > 监控 (By: Alejandro Cobar | Updated: 2020-07-27 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring)

问题 (Problem)

One of the things that a DBA needs to do is to monitor the status of SQL Server Agent Jobs. This can be done instance by instance, but this takes time. In this article we look at how to collect the last run information for all of the SQL Server instances that are being monitored with this solution.

DBA需要做的一件事是监视SQL Server代理作业的状态。可以逐个实例完成此操作，但这需要时间。在本文中，我们研究如何为使用此解决方案监视的所有SQL Server实例收集上次运行信息。

解 (Solution)

This module will collect the information of the very last execution status of each SQL Server Agent Job for all SQL Server instances within the inventory.MasterServerList table.

此模块将为清单.MasterServerList表中的所有SQL Server实例收集每个SQL Server代理作业的最后执行状态的信息。

先决条件 (Prerequisites)

In order to use this module, you need to create the core objects found here and also setup this module to collect SQL Agent job information.

为了使用此模块，您需要创建在此处找到的核心对象，还需要设置此模块以收集SQL Agent作业信息。

数据库对象 (Database Objects)

One new table will be created and it is a table to store the information of the last execution of the SQL Server Agent Jobs from each instance.

将创建一个新表，该表将存储每个实例中最后一次执行SQL Server代理作业的信息。

This is the structure of the table, so you can get an idea of what data will be stored.

这是表的结构，因此您可以了解将存储哪些数据。

Note: If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle the additional fields.

注意：如果要向该表添加更多字段，请确保调整PowerShell脚本中的结构并调整将处理其他字段的相应逻辑。

桌子 (Tables)

monitoring.Jobs
监视工作
- serverId - serverid ties back to inventory.MasterServerList
- serverId -serverid 绑定到清单。MasterServerList
- job_name - name of the SQL Agent jobjob_name -SQL代理作业的名称
- last_run_date_time - last run date and timelast_run_date_time-上次运行的日期和时间
- last_run_duration - length of the runlast_run_duration-运行时间
- last_run_status - status of the last runlast_run_status-上次运行的状态
- last_run_status_message - any messages from the runlast_run_status_message-运行中的任何消息
- next_run_date_time - next date and time the job will runnext_run_date_time-作业将运行的下一个日期和时间
- data_collection_timestamp - when data was last collected.data_collection_timestamp-上次收集数据的时间。

PowerShell脚本 (PowerShell Script)

The PowerShell script that creates the above object and inserts data into the monitoring.Jobs table is called:

创建上述对象并将数据插入到监视中的PowerShell脚本称为Jobs表：

Monitor-MSSQL-Instance-Jobs.ps1
Monitor-MSSQL-Instance-Jobs.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with, and it will also check that the inventory.Jobs table exists within the central repository database.

该脚本具有一些验证，可以帮助您检查是否缺少某些关键元素才能使脚本成功运行。例如，它将确认stocking.MasterServerList表存在并且至少注册了一个活动实例以便可以使用某些东西，并且还将检查中央存储库数据库中是否存在stock.Jobs表。。

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

如果您遵循了其他模块，则会注意到我们已经将所有对象存储在“ C：\ temp”中，但是您可以使用所需的任何文件夹。如果更改中央文件夹位置，则需要在以下PowerShell脚本中编辑第一行以指定新文件夹位置。

如何使用 (How to Use)

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

导航到创建文件的文件夹，然后可以运行PowerShell脚本，如下所示：

选项1 (Option 1)

Right click on Monitor-MSSQL-Instance-Jobs.ps1 and select Run with PowerShell.
右键单击Monitor-MSSQL-Instance-Jobs.ps1，然后选择“使用PowerShell运行”。

选项2 (Option 2)

Open a command window and navigate to the folder where you saved the above files and run
打开命令窗口，然后导航到保存上述文件的文件夹并运行

powershell "C:\temp\Monitor-MSSQL-Instance-Jobs.ps1"

选项3 (Option 3)

Schedule this as a SQL Server Agent Job to run the PowerShell script on a predefined basis.
将其安排为SQL Server代理作业，以在预定义的基础上运行PowerShell脚本。

选项4 (Option 4)

Schedule this as a Windows Task Scheduler job to run the PowerShell script on a predefined basis.
将其调度为Windows Task Scheduler作业，以在预定义的基础上运行PowerShell脚本。

检查数据库和对象的创建 (Check Creation of Database and Objects)

After running the PowerShell script, we can see the objects that are created.

运行PowerShell脚本后，我们可以看到创建的对象。

If we query both the inventory.Jobs table and monitoring.Jobs table, we can see the data that has been collected.

如果我们同时查询库存。作业表和监视。作业表，则可以看到已收集的数据。

Note: The PowerShell script will store only the information from the last execution, no historical data is retained. If you’d like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.

注意： PowerShell脚本将仅存储上一次执行的信息，不保留任何历史数据。 如果您想保留以前执行的信息，则必须修改脚本并将其调整为适合您的特定用例。

检查错误 (Checking for Errors)

To check for errors query the monitoring.ErrorLog table using the following query:

要检查错误，请使用以下查询查询Monitoring.ErrorLog表：

SELECT *
FROM monitoring.ErrorLog
WHERE script = Monitor-MSSQL-Instance-Jobs'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

如果您想知道出现错误SQL Server实例，则必须发出如下查询：

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS instance, 
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = Monitor-MSSQL-Instance-Jobs'

有用的查询 (Useful Queries)

By collecting all the data related to the execution of jobs across all your instances, you might answer things like:

通过收集所有实例中与作业执行相关的所有数据，您可能会回答以下问题：

哪些作业上次执行失败？ (Which jobs failed the last time that they were executed?)

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS instance,
   m.job_name,
   m.last_run_date_time, 
   m.last_run_duration,
   m.last_run_status,
   m.last_run_status_message,
   m.next_run_date_time
FROM monitoring.Jobs m
JOIN inventory.Jobs i ON m.job_name = i.job_name AND m.serverId = i.serverId
JOIN inventory.MasterServerList msl ON msl.serverId = m.serverId
WHERE i.is_enabled = 1 AND m.last_run_status <> 'Succeeded';

Note: Keep in mind that this will report failures only for the jobs within the inventory.Jobs table that are currently enabled. It really doesn’t make sense to know this information for the disabled jobs, but if that’s what you need, then feel free to make the necessary adjustments.

注意：请记住，这将仅报告当前启用的清单。作业表中的作业失败。 真正了解残疾人的工作信息并没有任何意义，但是如果您需要此信息，请随时进行必要的调整。

This is a very powerful one, and I highly recommend that you use this as a stepping stone to build a notification mechanism around this data. You could easily have a way to periodically know which jobs are not succeeding, across your entire infrastructure, without necessarily having to configure an email alert within each individual job at each individual instance (imagine you have hundreds/thousands of jobs within your scope without alerts setup).

这是一个非常强大的功能，我强烈建议您将其用作围绕此数据建立通知机制的垫脚石。您可以轻松地通过整个基础架构轻松地定期了解哪些作业不成功，而不必在每个单独的实例中的每个单独的作业中配置电子邮件警报(假设您的范围内有成百上千个没有警报的作业建立)。

哪些工作需要最长时间才能完成？ (Which jobs are taking the longest time to complete?)

SELECT TOP 10 *
FROM monitoring.Jobs
ORDER BY last_run_duration DESC

Note: If you are able to identify long running jobs, then probably you have a performance tuning opportunity there. It might not be necessarily the case, but you should be able to tell within your own particular environment.

注意：如果您能够确定长时间运行的作业，则可能在那里有性能调整的机会。 不一定是这种情况，但是您应该能够在自己的特定环境中分辨。