Getting SQL Server Agent Job status through Powershell


cls
#Import sql serer module
Import-Module sqlps -DisableNameChecking
#Provide instance name
$ServerNames="SQLServerNamedInstanceName"
$Server=New-Object `
-TypeName Microsoft.SQLServer.Management.Smo.Server `
-ArgumentList $ServerNames
$Jobs=$Server.JobServer.Jobs
$Jobs|Select-Object LastRunOutcome,OriginatingServer,Name,OwnerLoginName,LastRunDate,CurrentRunStatus,NextRunDate | Format-Table 


-----On multiple instances.
cls
#Import sql serer module
Import-Module sqlps -DisableNameChecking
#Provide instance name
$ServerNames=@("Server1","Server2","Server3")
$ServerNames|
ForEach-Object{
$ComputerName=$_
$Server=New-Object `
-TypeName Microsoft.SQLServer.Management.Smo.Server `
-ArgumentList $ComputerName
$Jobs=$Server.JobServer.Jobs
$Jobs|Select-Object LastRunOutcome,OriginatingServer,Name,OwnerLoginName,LastRunDate,CurrentRunStatus,NextRunDate | Where-Object -Property LastRunOutcome -EQ "Failed" |Format-Table 


Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server