19 SQL Server Agent administration
Now that you’re familiar with how the SQL Server Agent engine and PowerShell can best work together, we’ll discuss how dbatools can help you manage SQL Server Agent. As we discussed in chapter 1, the manual administration of SQL Server Agent jobs can be very time consuming, especially when you have many instances or many jobs to administer. This chapter provides all of the tools you need to administer your SQL Server Agent estate efficiently. You’ll learn how to gather all of the information about your SQL Server Agents, how to find a particular job in your estate easily, and how to retrieve and display the Agent job results and history.
DBAs are used to examining and administering the SQL Server Agent using SQL Server Management Studio (SSMS). dbatools enables you to perform the same tasks at the command line. Using the command line makes it easier to manage multiple objects or instances at once.
Figure 19.1 shows the view of the SQL Server Agent, which we have used to provide an order for the information in this section. It also demonstrates that there is no easy way to gather information about the objects on multiple instances in the same view with SSMS because the SQL Server Agent information is located under each node in the instance in Object Explorer.
We will start in a manner that should be becoming familiar to you now: by gathering information about your SQL Server Agent.
19.1 Listing SQL Server Agent information
Other teams and managers frequently ask DBAs to provide information about the estates that they manage, and SQL Server Agent jobs are an essential part of providing services to a business. Scheduling backups, database maintenance, and data load processes are common use cases for SQL Server Agent jobs, and being able to gather information quickly about these processes when questions are asked will save a DBA time. The first question will often be about the Agent jobs themselves, and it is also the first folder in the Object Explorer under SQL Server Agent, so let us begin there.
19.1.1 SQL Server Agent jobs
As a DBA, you often want to know which Agent jobs are on a particular instance. Imagine that you are a junior DBA, and during a discussion about a system, the senior DBA asks you, “What jobs are running on that instance?”
We think that by chapter 19, you may be able to guess the name of the command! It’s Get-DbaAgentJob. As your familiarity with PowerShell grows, the names and parameters for new commands become more evident, but remember that you can always use the lessons that you learned in chapter 2 to avoid guessing the names in dbatools or any other PowerShell module. The authors do so daily.
You now know that the command is Get-DbaAgentJob, and you can see in the next listing that the syntax is familiar, but don’t forget the lessons learned in chapter 2 and chapter 4. You can use Get-Help to understand how to use new commands—again, the authors do so every day, and so should you.
In the next listing, we’ll get the information about the SQL Server Agent jobs on a single instance.
Listing 19.1 Getting Agent jobs on a single instance
PS> Get-DbaAgentJob -SqlInstance sql01 ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : DatabaseBackup - SYSTEM_DATABASES - FULL ① Category : Database Maintenance ② OwnerLoginName : sqladmin ③ CurrentRunStatus : Idle ④ CurrentRunRetryAttempt : 0 Enabled : True ⑤ LastRunDate : 12/10/2019 01:00:39 ⑥ LastRunOutcome : Succeeded ⑦ HasSchedule : True ⑧ OperatorToEmail : CreateDate : 12/04/2019 12:28:18
① Tells us the name of the Agent job
② The category that the job belongs to
⑤ Shows whether the Agent is enabled
⑥ The last run date, no matter the outcome
⑧ Shows whether the job is associated with a schedule
If the conversation about that instance continued, and there were hundreds of SQL Server Agent jobs, it would be beneficial to filter them. It might be that you are interested in the jobs that are running against a particular database—the most significant database on that instance because you are considering the workload. To filter the results to show only the jobs running against that database, you would run the code in the next snippet. This will return only the Agent jobs with T-SQL steps that reference that database by filtering for the database named dbachecks.
Listing 19.2 Getting Agent jobs for a single database
PS> Get-DbaAgentJob -SqlInstance SQL01 -Database dbachecks ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : Gather dbachecks Category : dbachecks OwnerLoginName : adgsartori CurrentRunStatus : Idle CurrentRunRetryAttempt : 0 Enabled : True LastRunDate : 02/01/2020 16:09:39 LastRunOutcome : Succeeded HasSchedule : False OperatorToEmail : CreateDate : 02/01/2020 16:05:30 ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : Process dbachecks Category : dbachecks OwnerLoginName : adgsartori CurrentRunStatus : Idle CurrentRunRetryAttempt : 0 Enabled : True LastRunDate : 02/01/2020 16:09:50 LastRunOutcome : Succeeded HasSchedule : False OperatorToEmail : CreateDate : 02/01/2020 16:07:18
Using Get-DbaAgentJob with the -Database parameter is very useful for instances with a large number of jobs. However, it will filter only on T-SQL job steps. Readers should be aware that the default database is master, and people do not always configure job steps to use a specific database. You may find that if you filter by a user database, you will not return all of the jobs that interact with the database you have specified. You should plan to verify that the results of this command are as you would expect them.
19.1.2 SQL Server Agent alerts
Another function of the SQL Server Agent is to fire an alert. An alert is an automated response given when the application log has an event written to it, a performance counter exceeds a threshold, or a Windows Management Instrumentation (WMI) event is triggered. It is common for DBAs to set up alerts for SQL Server error log entries with a severity of 17 and above to notify them about severe issues with the instance. Table 19.1 shows details of various severity levels.
Table 19.1 SQL Server error log severity levels
Let’s view all of the alerts on a single instance with Get-DbaAgentAlert, as shown in the following listing.
Listing 19.3 Getting the list of Agent alerts
PS> Get-DbaAgentAlert -SqlInstance SQL01 ComputerName : SQL01 SqlInstance : SQL01 InstanceName : MSSQLSERVER Name : Severity 017 ID : 41 JobName : AlertType : SqlServerEvent CategoryName : [Uncategorized] Severity : 17 IsEnabled : True DelayBetweenResponses : 60 LastRaised : 2019-12-28 09:39:33.000 ① OccurrenceCount : 1 ComputerName : SQL01 SqlInstance : SQL01 InstanceName : MSSQLSERVER Name : Severity 018 ID : 42 JobName : AlertType : SqlServerEvent CategoryName : [Uncategorized] Severity : 18 IsEnabled : True DelayBetweenResponses : 60 LastRaised : 0001-01-01 00:00:00.000 ② OccurrenceCount : 0
Get-DbaAgentAlert answers the question, “What alerts are enabled on this instance?” Listing 19.3 shows how you can also identify the last time that an alert was raised ①.
You can expect that a follow-up question might be, “When was the last time there was an alert?” In T-SQL, you can identify the alerts that have been triggered by filtering on the last_occurrence_date column. You do this by selecting the column from the table where the column is not zero, as seen in the next code sample.
Listing 19.4 Using T-SQL to get the name of existing alerts that had an occurrence
You have to do a little more work when you do this with PowerShell because of the default value for LastRaised. As you saw in listing 19.3, it is a peculiar date ②! To be able to filter on the LastRaised property, you need to create a DateTime object, which you will use to filter the alerts, and finally, you will select the required property. You will get the alerts filter where the property is not equal to the datetime object, and then select the property, as shown here.
Listing 19.5 Getting alerts that had occurrences
PS> $NotRaised = Get-Date -Date '01-01-0001 00:00:00' Get-DbaAgentAlert -SqlInstance SQL01 | Where-Object LastRaised -ne $NotRaised ComputerName : SQL01 SqlInstance : SQL01 InstanceName : MSSQLSERVER Name : Severity 017 ID : 41 JobName : AlertType : SqlServerEvent CategoryName : [Uncategorized] Severity : 17 IsEnabled : True DelayBetweenResponses : 60 LastRaised : 2019-12-28 09:39:33.000 OccurrenceCount : 1
When discussing the alerts that exist on a system, you will also be interested in who will receive the alert. Notifying the correct people or systems responsible for any given type of alert is important, and operators enable this functionality.
An operator has an email sent when a SQL Server raises an alert. SQL Server operators are objects that represent the user accounts or groups that are going to receive the alerts. The email address for an operator is the destination email address for the email sent when the alert is raised.
When you need to list the operators for one or many instances, you can use Get-DbaOperator. Hopefully, by now, you can actually predict the syntax that will be used. Let’s list all of the operators on an instance, as shown next.
Listing 19.6 Getting Agent operators
PS> Get-DbaAgentOperator -SqlInstance SQL01 ComputerName : SQL01 SqlInstance : SQL01 InstanceName : MSSQLSERVER Name : DBA Team ① ID : 1 IsEnabled : True ② EmailAddress : [email protected] ③ LastEmail : 2019-12-28 09:39:33.000 ④
Listing 19.6 shows a single operator for this instance, named DBA Team ①. You can see that it is enabled ② and has an email address of [email protected] ③. You can also see the last time an operator had an email sent ④.
A SQL Server proxy account restricts the security context in which a SQL Server Agent job runs to the privileges of a credential that maps to a Windows user account. DBAs are then able to set up accounts that can run the Agent job but do not have further administrative permissions on the instance. As a DBA, you will want to list the proxies on an instance and identify the credentials linked to the proxies. This is often required when you need to understand which account requires access to a resource, such as a network share or a SQL database.
You can use a dbatools command to map the proxies to the credential. Instead of walking you through this, we are going to ask you to use the lessons you have learned so far in the book.
19.1.3 Finding specific Agent jobs
A large number of instances comes with a large number of Agent jobs. Other professionals expect a comprehensive knowledge of the estate the DBA administers, but remembering which instance has a particular job can be tricky, even for the best. dbatools enables you to give the illusion of having that knowledge with the Find-DbaAgentJob command.
Suppose that your company is responsible for processing data for a client who has many factories. The client is going to change the location of the FTP site that they use to transfer data to your company. You are the DBA, and the project manager asks you to provide the SQL Server instances that are processing that data and the Agent job names so that you can organize updating the jobs to use the new location. Let’s find those Agent jobs with Find-DbaAgentJob, as shown in the following listing.
Listing 19.7 Finding Agent jobs with FTP in their name on multiple instances
PS> $instances = "SQL01","SQL02","SQL03","SQL04","SQL05" PS> Find-DbaAgentJob -SqlInstance $instances -JobName *FTP* ComputerName : SQL02 InstanceName : MSSQLSERVER SqlInstance : SQL02 Name : Load data from PastaFactory FTP Category : DataLoad OwnerLoginName : OldSa CurrentRunStatus : Idle CurrentRunRetryAttempt : 0 Enabled : True LastRunDate : 03/01/2020 09:40:44 LastRunOutcome : Succeeded DateCreated : 03/01/2009 21:40:44 HasSchedule : True OperatorToEmail : CreateDate : 03/01/2009 21:40:44 ComputerName : SQL05 InstanceName : MSSQLSERVER SqlInstance : SQL05 Name : Load data from SauceFactory FTP Category : DataLoad OwnerLoginName : OldSa CurrentRunStatus : Idle CurrentRunRetryAttempt : 0 Enabled : True LastRunDate : 03/01/2020 08:40:44 LastRunOutcome : Succeeded DateCreated : 03/01/2009 21:42:18 HasSchedule : True OperatorToEmail : CreateDate : 03/01/2009 21:42:18
You will notice in listing 19.7 that when searching with Find-DbaAgentJob, the search term has a * before and after the search term. The * is a wildcard symbol, the same as %, if you are used to T-SQL.
In large estates with a large team supporting the SQL Server estate, it is easy to lose track of the status of Agent jobs. You want to know that your Agent jobs that back up your databases have a schedule, for example, or that after a maintenance window, someone had reenabled a vital data-loading Agent job. You can use additional parameters for Find-DbaAgentJob to easily find these jobs.
You are a senior DBA, and you want to quickly ensure that all of the jobs that will check the integrity of your production databases have a schedule so that you have peace of mind before your annual vacation. The next listing shows an example of the results that you will receive if there is a single Agent job without a schedule. You can quickly set the schedule for the one missing job and go on vacation happily.
Listing 19.8 Finding Agent jobs without a schedule
PS> $splatFindAgentJob = @{ SqlInstance = 'SQL01','SQL02','SQL03','SQL04','SQL05' JobName = "*Integrity*" IsNotScheduled = $true } PS> Find-DbaAgentJob @splatFindAgentJob ComputerName : SQL01 InstanceName : MSSQLSERVER SqlInstance : SQL01 Name : DatabaseIntegrityCheck - USER_DATABASES Category : Database Maintenance OwnerLoginName : OldSa CurrentRunStatus : Idle CurrentRunRetryAttempt : 0 Enabled : True LastRunDate : 27/12/2019 07:34:56 LastRunOutcome : Succeeded DateCreated : 04/12/2019 12:28:18 HasSchedule : False OperatorToEmail : CreateDate : 04/12/2019 12:28:18
Now that you know how to return information about the Agent on your estate, the most crucial information that you will want to retrieve is the Agent job results.
19.2 Agent job results and history
Why have we ordered this chapter in this way? We can hear people shouting that the results of the Agent jobs are the most important aspect. Why have we chosen to discuss it later in the chapter?
The msdb database holds information about the Agent job outcomes and can have a significant amount of data in it. With any large dataset, you will want to filter the results as early as possible to improve performance. With T-SQL, you would do this with a WHERE clause, and the SQL engine will, as long as there are indexes that can filter for that clause, return only the rows that match.
PowerShell does not work in the same way. You can use Where-Object in the pipeline to filter output as we discussed in chapter 5, but PowerShell will get all of the results and then pass them through the pipeline to the Where-Object.
We recommend that you filter the results so that they are easier to manage and quicker to provide meaningful information. One way that you can do this is to use Get-DbaAgentJob to filter by job name or category as described in the SQL Server Agent jobs section (section 19.1.1). Another method is to use Find-DbaAgentJob to filter by time with the -Since parameter.
19.2.1 Agent job results
When you arrive at work in the morning, the first, most crucial information that you want to see as a DBA is the results of last night’s Agent jobs. If you have a large estate, you probably already have a monitoring system or report set up to provide this information. Let’s return to the earlier scenario with Agent jobs that had the FTP site altered. In the morning, after the change overnight, you would want to know that the jobs had succeeded. You use the following code to find the FTP jobs and select the outcome.
Listing 19.9 Returning the last run and outcome for jobs with FTP in their name
PS> Find-DbaAgentJob -SqlInstance $instances -JobName *ftp* | Select SqlInstance, JobName, LastRunDate, LastRunOutcome SqlInstance JobName LastRunDate LastRunOutcome ----------- ------- ----------- -------------- SQL02 Load data from PastaFactory FTP 03/01/2020 04:38:12 Succeeded SQL03 Load data from PizzaFactory FTP 03/01/2020 04:38:13 Succeeded SQL04 Load data from SausageFactory... 03/01/2020 04:38:14 Succeeded SQL05 Load data from SauceFactory... 03/01/2020 04:38:15 Succeeded
You are happy with that result because it shows that all of the FTP jobs have succeeded, but you also want to check that the jobs used the correct FTP site, the new one. You want to check the job history, as follows.
Listing 19.10 Returning the history of all jobs with FTP in their name
PS> $midnight = [datetime]::Today PS> Find-DbaAgentJob -SqlInstance $instances -JobName *ftp* | Get-DbaAgentJobHistory -StartDate $midnight ComputerName : SQL02 InstanceName : MSSQLSERVER SqlInstance : SQL02 Job : Load data from PastaFactory FTP StepName : (Job outcome) RunDate : 03/01/2020 04:38:12 StartDate : 2020-01-03 04:38:12.000 EndDate : 2020-01-03 04:38:12.000 Duration : 12456 ms Status : Succeeded OperatorEmailed : Message : The job succeeded. The last step to run was step 1 (Lo... ComputerName : SQL02 InstanceName : MSSQLSERVER SqlInstance : SQL02 Job : Load data from PastaFactory FTP StepName : Load from FTP RunDate : 03/01/2020 14:38:12 StartDate : 2020-01-03 14:38:12.000 EndDate : 2020-01-03 14:58:12.000 Duration : 12456 ms Status : Succeeded OperatorEmailed : Message : Executed as user: adsqlsvc. Connecting to FTP site ft...
When you examine the Message property, you see that the output of the job shows that the new FTP site, ftp.pastafactory.it, has been used. You can also see the duration of each job step.
You can use Get-DbaAgentJobHistory to return the history of Agent jobs. Remember that this can be a significant amount of data. We strongly recommend that you filter the jobs whose history you want to retrieve before piping to Get-DbaAgentJobHistory and that you understand and use the -StartDate and -EndDate parameters to filter the results.
19.2.2 Time line
It is useful to visualize the run times of Agent jobs to ensure that they are not competing for resources because they are running at the same time. Marcin Gminski has enabled this for you. You will need to pipe four commands together to do this. Let’s get the Agent job history for an instance for the last three days and create an HTML report to show the times and duration as follows.
Listing 19.11 Generating a report for the Agent job history for the last three days
PS> $threeDaysAgo = [datetime]::Today.AddDays(-3) PS> Find-DbaAgentJob -SqlInstance sql01 | Get-DbaAgentJobHistory -StartDate $threeDaysAgo | ConvertTo-DbaTimeline | Out-File -FilePath c: empjobs.html -Encoding ASCII
You have gathered all the Agent jobs on the instance, got the history of the jobs for the last three days, converted that to a time line, and exported it to a file. It is essential to notice that the file encoding needs to be specified as ASCII for this to work. To examine the results and identify any issues, you open the file in a browser, as shown in figure 19.2.
We think that this is not only useful but beautiful, too!
You have learned a lot about administering SQL Server Agent in this chapter, from gathering the information about the Agent and all of its properties to searching for jobs and returning the outcome and the detailed history. Finally, you learned how to create a time line for Agent job runs. The next chapter will concentrate on creating and altering Agent jobs and the best way we have found to run dbatools commands via the SQL Server Agent.