SQL Server Automation Operations Series-Monitor run-batch job status (Power Shell)

Requirements Description

In our production environment, in most cases, we need to have our own operation and maintenance system, including the detection of their own health status. In the event of an exception, an early warning is required, and the form of notification is generally communicated by e-mail.

The basic configuration of messages in SQL Server has been analyzed in the previous article, and this feature will be used to monitor running batches for multiple servers.

This article realizes

1, daily check the server running status of SQL Server run batch job, if the run batch failed, then send an email to tell the administrator failed details

2, solve multiple servers simultaneously check

Monitoring Scripts

First, let's solve the second problem with more than one server:

<1> General monitoring We need to monitor the job of many servers, so we need to generate a configuration file for the amount of server control.

<computernames>        <computername>                wuxuelei-pc        </computername></ Computernames> 

Profile Name: Computername.xml, this solves a lot of server problems, only need to add in the configuration file can be, because I am testing locally, so I configured my local computer

<2> Use the Power shell script to capture the status of each server's job and organize the job's run time, running status, description, and error steps to form a summary message.

The script is as follows:

$server ="(local)"$uid="SA"$db="Master"$pwd="Password"$mailprfname="Testmail"$recipients="[email protected]"$subject="boss, go and see these servers job run failed! "$computernamexml="F:\PowerShell\ sending mail \computername.xml"function getServerName ($xmlpath) {$xml= [XML] (get-Content $xmlpath) $return= New-object collections.generic.list[string]     for($i =0; $i-lt $xml. computernames.ChildNodes.Count; $i + +)    {        if($xml. Computernames.childnodes.count-eq1) {$CP= [string] $xml. computernames.computername}Else{$CP= [string] $xml. computernames.computername[$i]} $return. ADD ($CP. Trim ())} $return}function Getaltercounter ($xmlpath) {$xml= [XML] (get-Content $xmlpath) $return= New-object collections.generic.list[string] $list=$xml. Counters.counter}function createalter ($message) {$SqlConnection= new-Object System.Data.SqlClient.SqlConnection $CnnString="Server = $server; Database = $db; User Id = $uid; Password = $pwd"$SqlConnection. ConnectionString=$CnnString $CC=$SqlConnection. CreateCommand (); if(-not ($SqlConnection. state-like"Open") {$SqlConnection. Open ()} $cc. CommandText="EXEC msdb: Sp_send_dbmail@profile_name ='$mailprfname', @recipients='$recipients', @body='$message', @subject='$subject'            "$CC. ExecuteNonQuery () | out-NULL$SqlConnection. Close ();} $report=""$item= New-object collections.generic.list[string] $names=getServerName ($computernamexml)foreach($CPinch$names) {$srv=new-object"Microsoft.SqlServer.Management.Smo.Server" "(local)"$item= $srv. JobServer.Jobs |where-Object{$_.lastrunoutcome-eq"Failed"-and $_.isenabled-eq $TRUE} |Selectoriginatingserver,name,description,lastrunoutcome,lastrundate,jobsteps #Write-Host $item. Jobsteps.name $report+="Server:"+ $item. originatingserver+"Job Name:"+ $item. name+"Job Description:"+ $item. Description +"job last Run state:"+ $item. LastRunOutcome +"Job last Run time:"+ $item. LastRunDate +"Job failed step Name:"+ $item. Jobsteps.name +"' n"} #生产警告CreateAlter $report

Through the above script, generate run batch task, you can regularly monitor the job of multiple servers.

Of course, it is recommended that after all job runs are complete, the test is done.

In the code above, there are two technical points:

1, need to configure their own SQL Server mail agent, specific methods refer to my previous article: click here;

2, need to configure their own run-batch plan, methods of their own online search, very simple.

This article is monitored by the job status, using the last one we created the job, run the batch must fail.

As follows

Click to see the message details:

The failure information given is quite detailed.

In addition, because I only set up a failed job, so the message just send a bar.

In fact, there are some states that can be monitored for this monitoring:

1, such as: can be specified on the server part of the job to monitor

2, monitoring the status of the job: failure or normal, etc.

The above content, can be flexibly configured according to your needs.

Conclusion

This article lists the use of PowerShell for automated operations and testing. As a catalyst, I have another need to achieve their own flexibility.

In addition to the job, generally except the SQL Server Agent will exist, and part of the operating system's scheduled tasks also need to be detected, we later the article to resolve this issue.

There is a wide range of SQL Server Automation operations and testing, many of which are based on daily experience, from manual to automated processes step-by-step.

Later in this article, we'll take a deeper look at automating optimized operations for SQL Server. Interested children's shoes can be noticed in advance.

At the end of the article, we give some other monitoring methods of the series, which are original

What counters SQL Server needs to monitor

SQL Server Automation Operations series-Monitoring Performance metrics script (Power Shell)

SQL Server Automation Operations series-monitoring disk remaining space and SQL Server error logs (Power Shell)

SQL Server Automation Operations series-about email notifications that's something (. NET Developer Benefits)

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

SQL Server Automation Operations Series-Monitor run-batch job status (Power Shell)