This month we’ll conclude our mini-series about SQL Server Agent by showing you how to create scripts that wait for jobs (or job steps) to finish executing and scripts that report on the status of the SQL Server Agent service. But first we’ll quickly revisit Agent job reporting to discuss the most efficient way to filter historical records when it contains numerous records.
Filtering Large Numbers of Records
In last month’s article “Agent Jobs Reporting,” we showed you how to build simple reports about the execution status of jobs using Windows PowerShell and SQL Server Management Objects (SMO). To filter the historical records, we used the EnumJobHistory() method to fully populate a collection and streamed the collection through the PowerShell pipeline to the Where-Object cmdlet, which filtered the records in the collection.
Although this is a very straightforward way of filtering in PowerShell, it may not be efficient enough in cases where the history table contains large number of records. Companies might have numerous records because they never tuned the history retention configuration or they need to keep a lot of records for internal audit purposes. Whatever the reason, in these cases, it’s much more efficient to declare upfront a filter that can be passed to the EnumJobHistory() method. This way, SMO applies query conditions behind the scenes instead of creating a collection that contains all the records.
As an example, let’s say you want a report detailing only those jobs that failed to execute in the last 24 hours. Listing 1 shows how you can build the filter upfront, then pass it to the EnumJobHistory() method.
$Hours = 24; $DateFrom = (Get-Date).AddHours(($Hours * -1)); $JobHistoryFilter = ` New-Object Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter; $JobHistoryFilter.StartRunDate = $DateFrom; $JobHistoryFilter.EndRunDate = Get-Date; $JobHistoryFilter.OutcomeTypes = ` [Microsoft.SqlServer.Management.Smo.Agent.CompletionResult]::Failed $JobsHistory = $SQLInstance.JobServer.EnumJobHistory($JobHistoryFilter);
Waiting for a Job to Complete
When dealing with SQL Server Agent, you may need to wait for a job to finish executing. In T-SQL code, this can be done by calling the sp_help_job system stored procedure in a loop and waiting between each call. In PowerShell, SMO can be used in a similar way. Before looking at that PowerShell code, though, let’s create a simple job with a single T-SQL command step that waits for 1 minute, as follows:
WAITFOR DELAY '00:01:00'
This will be the job you’re going to wait for. The complete T-SQL code to create this job is available in the Script-1-WaitFor.
To wait for the job to finish executing, you can use the CurrentRunStatus property of the Job object in a while loop and check to see whether the property’s value is “Idle”, as in the following code fragment:
while( $SQLInstance.JobServer.Jobs[$JobName].CurrentRunStatus -ne '[Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle }
Inside the loop, you should call the Start-Sleep cmdlet to pause the script for a specified amount of time between calls, like the following code shows:
Start-Sleep -Seconds $CheckIntervalSeconds;
Using the Start-Sleep cmdlet prevents high CPU consumption caused by a “tight” loop that is polling the property value as fast as it can. Moreover, inside the loop, you should call the Refresh() method of the Job object to refresh the CurrentRunStatus property, as shown by the following code fragment:
$SQLInstance.JobServer.Jobs[$JobName].Refresh();
In many real-life cases, you also need a way to terminate the script after a certain amount of time has passed, even if the job is still running. You can easily translate the time period into a number of iterations that wait the same amount of time.
Listing 2 shows the complete solution. This solution uses the Get-SQLInstance function to initialize and log into a SQL Server instance. We introduced this function in our article “Automating databases migration” (February 2011) . The Get-SQLInstance function supports both Windows and SQL Server authentication methods. It requires only one parameter: the instance name. You can find the complete PowerShell code in the Script-2-Get-WMI-information-Static.
$Move_Button.Enabled = $false; $ChangePath_Button.Enabled = $false; $Databases_ComboBox.Enabled = $false; $GetFiles_Button.Enabled = $false;
Please note that if the “WaitFor” job is not running when this script is executed (i.e., the job’s status is “Idle”), the script completes immediately. This may or may not be the desired intent. You should change the script to meet your needs.
If you run the job first and the PowerShell script immediately afterward, the script will end after 20 seconds reporting that the job didn’t finish in the specified time period. If you increase the total wait time to more than 60 seconds by increasing the number or length of the iterations,the script will end after the allotted time, reporting that the job terminated.
#Parameters
$SQLInstanceName = ".prod1";
$JobName = "WaitFor"
$CheckIntervalSeconds = 10;
$Iterations = 2;
#main
$SQLInstance = Get-SQLInstance $SQLInstanceName;
while (
$SQLInstance.JobServer.Jobs[$JobName].CurrentRunStatus -ne `
[Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle `
-and $Iterations -gt 0
)
{
Start-Sleep -Seconds $CheckIntervalSeconds;
--$Iterations;
$SQLInstance.JobServer.Jobs[$JobName].Refresh();
};
if (
$SQLInstance.JobServer.Jobs[$JobName].CurrentRunStatus -ne `
[Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle
)
{
Write-Output "Job $JobName didn't finish within the specified time period!";
}
else
{
Write-Output "Job $JobName terminated!";
};
If you don’t want to manually start the job using SQL Server Management Studio (SSMS), you can add the code in Listing 3 to the PowerShell script just before the while loop. After this code starts the job, it needs to wait until the run status changes from “Idle” to “Executing” before continuing. Under normal conditions, it takes a lot less than a second, which is why the code sleeps for just 10 milliseconds between each poll.
$SQLInstance.JobServer.Jobs[$JobName].Start();
while (
$SQLInstance.JobServer.Jobs[$JobName].CurrentRunStatus -eq `
[Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle `
)
{
Start-Sleep -Milliseconds 10;
$SQLInstance.JobServer.Jobs[$JobName].Refresh();
};
Waiting for a Job Step to Complete
In some cases, you might need to wait for a job step to finish executing. Starting with SQL Server 2008, SQL Server Agent supports job steps of type PowerShell Script, so you can leverage the previous code with some minor changes.
As we discussed in “Logging with SQL Server Agent” (April 2011) , SQL Server Agent supports Agent tokens. In job step code, they’re placeholders for environmental values that are substituted at runtime just before execution. So, instead of using the Get-SQLInstance function, you can simply connect using Windows authentication, passing in the current server name through an Agent token, like the following code fragment shows:
#main $SQLInstance = New-Object "Microsoft.SQLServer.Management.Smo.Server" '"'$(ESCAPE_NONE(SVR))"; $SQLInstance.ConnectionContext.Connect();
Another thing you need to change is how the script ends. In most cases, you probably want to report a status of “Failure” for the step’s execution if the job you’re waiting for doesn’t finish in the specified time period. This can be done by throwing an exception, as shown in the following example:
if(
$SQLInstance.JobServer.Jobs[$JobName].CurrentRunStatus -ne '[Microsoft.SQLServer.Management.Smo.Agent.JobExecutionStatus]::Idle
)
{
Throw("The Job $JobName didn't finish within the specified time period!");
}
You can find the complete T-SQL code to create the job with the PowerShell Script step in theScript-3-Waiting-For-Job
Getting the SQL Server Agent Service Status
Another common administrative task is to check the status of the SQL Server Agent service. This can be done by using the Get-Service cmdlet and specifying a filter, as following code fragment shows:
Get-Service -DisplayName "*SQL Server Agent*"
Status Name DisplayName ------ ---- ----------- Stopped SQLAgent$CONFIG SQL Server Agent (CONFIG) Running SQLAgent$PROD1 SQL Server Agent (PROD1) Stopped SQLAgent$PROD2 SQL Server Agent (PROD2) Stopped SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Figure 1 shows an example of the output when run on a machine with four SQL Server instances.
Using the Get-Service cmdlet with a filter is good if you want to simply check the status of the SQL Server Agent services on the local machine. But what if you need to check the status for several remote machines? Fortunately, the cmdlet supports the ComputerName parameter. To get the status information from remote systems, so you need to create a string collection of computer names to be checked, like in the following example:
"P846", "P853" `
| % { Get-Service -DisplayName "*SQL Server Agent*" `
-ComputerName $_ }
“In this example, the status of the SQL Server Agent service is checked on two remote servers named P846 and P853. Figure 2 shows the results.
Status Name DisplayName ------ ---- ----------- Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER) Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
As you can see, there’s no way to distinguish which service is running on which server. You can easily solve this problem by using the Select-Object cmdlet to specify the properties of interest, like in the following code:
"P846", "P853" `
| % { Get-Service -DisplayName "*SQL Server Agent*" -ComputerName $_ } `
| Select MachineName, Status, Name, DisplayName `
| Format-Table –AutoSize;
MachineName Status Name DisplayName ----------- ------ ---- ----------- P846 Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER) P853 Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Figure 3 shows the results.
Alternatively, you can use the Format-Table cmdlet’s Property parameter to output only the properties you’re interested in, as the following example shows:
"846", "P853" `
| % { Get-Service -DisplayName "*SQL Server Agent*" -ComputerName $_ } `
| Format-Table -Property MachineName, Status, Name, DisplayName -AutoSize;
This code gives the same results shown in Figure 3. One problem with both approaches is that the list of server names is used directly in the script. A better strategy is to put the server names in a text file and pass the text file’s name using the param directive, as follows:
param ( [string] $ServerList = "C:TempServerList.txt" );
However, this passes only the filename; you still need to read the server names from the file. This can be done by using the Get-Content cmdlet to build a collection of names, as in the following example:
$ServerCollection = Get-Content $ServerList;
There’s something we haven’t covered yet. What happens if you pass in the name of a non-existent server to the Get-Service cmdlet or one of the servers is not reachable? The cmdlet throws an exception, which you should handle. Even better, you can build a generic function called Get-AgentState that contains both the filtering logic and the exception handling, as shown in listing 8.
function Get-AgentState ($Server)
{
try {
Get-Service -DisplayName "*SQL Server Agent*" -ComputerName $Server `
| Select MachineName, Status, Name, DisplayName;
}
catch {
Write-Warning ("Error connecting to $Server");
};
};
Once you have the generic function, you can simply stream the collection of server names read from the text file, as in the following example:
param (
[string] $ServerList = "C:TempServerList.txt"
);
Get-Content $ServerList | % { Get-AgentState $_ } `
| Format-Table -AutoSize;
Alternatively, you can use the Get-AgentState.ps1 script directly, passing in the name of the text file that contains the server names. Figure 4 shows the results. Please note that P839 and P840 are the physical nodes in a two-way, active-active SQL Server cluster. Figure 4 shows both SQL Server virtual instances; thus, both SQL Server Agent services are running on the same physical node (P839). You can find the complete PowerShell code in the Script-4-Get-AgentState.
If you prefer to have the resulting collection include the servers that failed to connect, you can substitute the call to the Write-Warning cmdlet in the catch directive with the following code:
1 | Select @{Name = "MachineName"; Expression = { $Server }} `
, @{Name = "Status"; Expression = { "N/A" }} `
, @{Name = "Name"; Expression = { "N/A" }} `
, @{Name = "DisplayName"; Expression = { "N/A" }};
Otherwise, you can change the Get-AgentState function to build the resulting collection, as seen here:
function Get-AgentState ($Server)
{
$AgentState = new-object System.Object;
try {
$AgentState = Get-Service -DisplayName "*SQL Server Agent*" -ComputerName $Server `
| Select MachineName, Status, Name, DisplayName;
}
catch {
$AgentState | add-member -membertype noteproperty -name MachineName -value $Server;
$AgentState | add-member -membertype noteproperty -name Status -value 'N/A';
$AgentState | add-member -membertype noteproperty -name Name -value 'N/A';
$AgentState | add-member -membertype noteproperty -name DisplayName -value 'N/A';
};
$AgentState;
};
Conclusion
In this article, we covered some more useful techniques when dealing with SQL Server Agent. Because of the Agent’s central role in automating administrative tasks, we’ll likely feature more examples in the future. Until then, we’d love to hear from you—especially about which topics you’d like us to cover next.
Article co-authors: Herbert Albert and Gianluca Hotz.
