One of the things that a DBA needs to do is to monitor the status of SQL ServerAgent Jobs. This can be done instance by instance, but this takes time. Inthis article we look at how to collect the last run information for all of the SQLServer instances that are being monitored with this solution.
DBA需要做的一件事是監視SQL Server代理作業的狀態。 可以逐個實例完成此操作，但這需要時間。 在本文中，我們研究如何為使用此解決方案監視的所有SQL Server實例收集上次運行信息。
This module will collect the information of the very last execution status ofeach SQL Server Agent Job for all SQL Server instances within the inventory.MasterServerListtable.
此模塊將為清單.MasterServerList表中的所有SQL Server實例收集每個SQL Server代理作業的最後執行狀態的信息。
In order to use this module, you need to createthe core objects found here and also setupthis module to collect SQL Agent job information.
為了使用此模塊，您需要創建在此處找到的核心對象 ，還需要設置此模塊以收集SQL Agent作業信息 。
One new table will be created and it is a table to store the information of thelast 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 willbe stored.
這是表的結構，因此您可以了解將存儲哪些數據。
Note: If you want to add more fields to this table, make sure to adjust the structurewithin the PowerShell script and adapt the respective logic that will handle theadditional fields.
注意：如果要向該表添加更多欄位，請確保調整PowerShell腳本中的結構並調整將處理其他欄位的相應邏輯。
monitoring.Jobs
監視工作
The PowerShell script that creates the above object and inserts data into themonitoring.Jobs table is called:
創建上述對象並將數據插入到監視中的PowerShell腳本稱為Jobs表：
The script has some validations that will help you check if some key elementsare missing for the script to run successfully. For instance, it will confirm thatthe inventory.MasterServerList table exists and that it has at least 1 active instanceregistered to be able to have something to work with, and it will also check thatthe 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 beenstoring 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 firstline in the following PowerShell script to specify the new folder location.
如果您遵循了其他模塊，則會注意到我們已經將所有對象存儲在「 C：\ temp」中，但是您可以使用所需的任何文件夾。 如果更改中央文件夾位置，則需要在以下PowerShell腳本中編輯第一行以指定新文件夾位置。
Navigate to the folder where you created the files and you can run the PowerShellscript as follows:
導航到創建文件的文件夾，然後可以運行PowerShell腳本，如下所示：
powershell "C:\temp\Monitor-MSSQL-Instance-Jobs.ps1"
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 seethe data that has been collected.
如果我們同時查詢庫存。作業表和監視。作業表，則可以看到已收集的數據。
Note: The PowerShell script will store only the informationfrom the last execution, no historical data is retained. If you』d like to keep information from previousexecutions, you would have to modify the script and adapt it to your particularuse case.
注意： PowerShell腳本將僅存儲上一次執行的信息，不保留任何歷史數據。 如果您想保留以前執行的信息，則必須修改腳本並將其調整為適合您的特定用例。
To check for errors query the monitoring.ErrorLog table using the following query:
要檢查錯誤，請使用以下查詢查詢Monitoring.ErrorLog表：
SELECT *FROM monitoring.ErrorLogWHERE script = Monitor-MSSQL-Instance-Jobs'
If you』d like to know the SQL Server instance that got the errors, youwould 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_timestampFROM monitoring.ErrorLog eJOIN inventory.MasterServerList msl ON msl.serverId = e.serverIdWHERE e.script = Monitor-MSSQL-Instance-Jobs'
By collecting all the data related to the execution of jobs across all your instances,you might answer things like:
通過收集所有實例中與作業執行相關的所有數據，您可能會回答以下問題：
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_timeFROM monitoring.Jobs mJOIN inventory.Jobs i ON m.job_name = i.job_name AND m.serverId = i.serverIdJOIN inventory.MasterServerList msl ON msl.serverId = m.serverIdWHERE i.is_enabled = 1 AND m.last_run_status <> 'Succeeded';
Note: Keep in mind that this will report failures only for the jobs within theinventory.Jobs table that are currently enabled. It really doesn’t make senseto 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 steppingstone to build a notification mechanism around this data. You could easily havea way to periodically know which jobs are not succeeding, across your entire infrastructure,without necessarily having to configure an email alert within each individual jobat each individual instance (imagine you have hundreds/thousands of jobs withinyour scope without alerts setup).
這是一個非常強大的功能，我強烈建議您將其用作圍繞此數據建立通知機制的墊腳石。 您可以輕鬆地通過整個基礎架構輕鬆地定期了解哪些作業不成功，而不必在每個單獨的實例中的每個單獨的作業中配置電子郵件警報(假設您的範圍內有成百上千個沒有警報的作業建立)。
SELECT TOP 10 *FROM monitoring.JobsORDER BY last_run_duration DESC
Note: If you are able to identify long running jobs, then probably you havea performancetuning opportunity there. It might not be necessarily the case, but you shouldbe able to tell within your own particular environment.
注意：如果您能夠確定長時間運行的作業，則可能在那裡有性能調整的機會。 不一定是這種情況，但是您應該能夠在自己的特定環境中分辨。
Download the scripts for this module
下載此模塊的腳本
翻譯自: https://www.mssqltips.com/sqlservertip/6495/sql-server-agent-job-monitoring-for-last-execution-with-powershell/