Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Add SQL Server Agent Management to SQL Operations Studio


By:   |   Last Updated: 2018-10-01   |   Comments   |   Related Tips: More > SQL Operations Studio

Problem

SQL Operations Studio is a new tool that runs on Windows, macOS, and Linux, and aims to simplify management for SQL Server on Windows, Linux, Azure SQL Database, and Azure SQL Data Warehouse. SQL Operations Studio provides support for extensions, which is a nice way to extend features and enhancements. Let's explore how to setup SQL Server Agent for SQL Operations Studio.

Solution

Extensions in SQL Server are a way to provide add-on functionality by Microsoft as well as third party users. As we discussed earlier in my tips about SQL Operations Studio, below are the SQL Operations Studio versions.

Release Date Release Version Description
November 15, 2017 0.23.6 Initial release of SQL Operations Studio
December 19,2017 0.24.1 December Public Preview
January 17,2018 0.25.4 January Public Preview
February 15,2018 0.26.7 February Public Preview
March 28,2018 0.27.3 March Public Preview
April 25, 2018 0.28.6 April Public Preview
May 7, 2018 0.29.3 May Public Preview
June 20,2018 0.30.6 June Public Preview
July 19, 2018 0.31.4 July Public Preview
August 30, 2018 0.32.8 August Public Preview

To view the SQL Operations Studio version, go to Help > About.

Image 1: SQL Operation Studio Version.

In this previous tip we explored these extensions:

  • Server Reports
  • WhoIsActive Extension
  • RedGate SQL Search Extension

SQL Operations Studio does not support SQL Server Agent by default.  I feel that without SQL Server Agent this tool is not complete. In this tip, we'll explore how setup the SQL Server Agent extension in SQL Operations Studio.

SQL Server Agent Extension

SQL Operations Studio provides supports for SQL Server Agent using the SQL Server Agent extension.

With this extension, we can:

  • View SQL Server Agent Jobs Configured on a SQL Server
  • View Job History with job execution results
  • Basic Job Control to start and stop jobs
  • Create or Edit SQL Server Agent Jobs
  • View, Configure Operators, Alerts
  • Create Proxy accounts

To install SQL Server Agent, click on the extension in the marketplace.

Image 2: SQL Server Agent Extension help page

This opens a web page in a new tab that shows the basic details about the SQL Server Agent extension. Click on Install and Reload.

. Image 3: Reload SQL Server Agent Extension

Now we can see that the SQL Server Agent Extension is installed.

Image 4: SQL Server Agent Extension in Installed section

In order to launch the SQL Server Agent extension, connect to the database instance, right click on the instance and select the Manage option.

Image 5: Launch SQL Server Agent Extension

We can see SQL Agent as a tab in the server dashboard.

Image 6: View SQL Server Agent Extension

Click on the SQL Agent tab and it opens the below window.

Image 7: View SQL Server Agent Extension

In the left sidebar, we can see these options:

  1. Jobs
  2. Alerts
  3. Operators
  4. Proxies

Let's explore the SQL Server Agent extension options and see how useful SQL Operations Studio can be.

Jobs in SQL Server Agent Extension

When we launch SQL Server Agent Extension, by default it opens up in the Job tab, which shows all the configured jobs for that particular instance and the history.

In the jobs dashboard, we can see the below important columns:

  • Name: Job Name
  • Last Run: Last execution time of the job.
  • Next Run: If the job is scheduled, it shows the next run time of the job.
  • Enabled: If the job is enabled, then yes else no
  • Category: If we have defined a job category in Job configuration, it shows the category from the below list. The default value is [Uncategorized [Local]]. Categories help filter jobs based on a particular category.
Image 8: Category in SQL Server Jobs
  • Scheduled: If the Job is scheduled then Yes else No.
  • Last Run Outcome: The last outcome of the job execution. It can show these values: failed, succeeded, retry, canceled, in progress. The status of Unknown shows that the job has not been run.
  • Previous Runs: An interesting and nice enhancement to view job run trends. This shows the graphical history of the job execution with Green and Red color bars.
    • Green Bars: For successful job execution
    • Red bars: For failed job execution

For example, in the below screenshot, we can see that two instances of the job failed while others were successful.

Image 9: View failed job

You can check the job execution time by placing the cursor over the bar.

Image 10: View Timelines

You can also see the height of the bars are different. The height of the bar depends upon the job execution time. We can see the difference in the execution time of a job.

Image 11: Height of the bars in Job

In the jobs, we can also see a bar on the left as shown below. This bar shows the last job run outcome.

Image 12: Last successful job history bar

To refresh the jobs dashboard, click on the Refresh icon towards the upper right corner.

Image 13: Refresh SQL Jobs

Now let me run a job and make it fail to see the behavior in the dashboard.

We can see the below changes in the SQL Server Agent job dashboard.

  • Last run outcome bar becomes Red
  • This also shows the failed job reason
  • Previous runs also adds a failed bar
Image 14: Failed job error message

If we want to view the detailed job history, click on the particular job. For example, below we can see the job history for the DB Backup Job.

Image 15: Detailed job history

Scroll down to the screen and we can see the step history for the job. Currently, there seems to be some formatting issue as we cannot scroll to the left and right to view the complete message. It should be fixed in future releases of SQL Operations Studio.

Image16: Error in detailed job history

However, we can double-click on the message or use Ctrl+C to copy the message content.  We can paste this message in Notepad or another editor to analyze it.

Executed as user: NT SERVICE\SQLAgent$SQL2017. Cannot open backup device 'C:\mssqltips\backup\master.bak'. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Now I have created the backup folder in the desired location. Now, we can run the job from SQL Operations Studio itself. In the detailed job history for the particular job, there is an option to run the job.

Image 17: Run or execute the job

Once the job is started, we get a pop message that the job started successfully.

Image 18: Job start message

Click on Run to execute the job and we get the message - DB Backup Job: the job was successfully started.

We can see the job is successful now.

Image 19: Successful job

Edit and Delete a Job using SQL Operations Studio

Right clicking on a job gives options to edit and delete the job.

Image 20: Edit or delete job

Click on Edit Job to make changes to the job. This opens up the Edit Job window.

Image 21: Edit job screen

We can make changes to the job and save it. Suppose we want to make below change:

  • Disable the job
  • Mention the reason in the description
  • Change Job category to Database Maintenance

In the edit job screen, change the category from the dropdown menu, put the description in the description text box and remove the checkbox from Enabled. Click OK to make the changes.

Image 22: Make changes in the job

We can verify the changes as shown below.

Image 23: Verify the changes done in Edit job

Filter the SQL Server Agent Jobs

Suppose we have a large number of jobs in the database instance. It would be difficult to find a particular job from the long list. The SQL Operations Studio SQL Agent extension is intelligent in doing that. We can filter out the result from any column we want.

As shown below, each column has the option to filter the results.

Image 24: Filter job options

We can filter the results based on the job name, last run, next run, Enabled, Status, Category, last run outcome.

Suppose I want to see only enabled jobs, so click on the icon near the Enabled column. This opens up the mini window to either sort the results in descending or ascending order. We can also select Yes (to see enabled jobs) and No (to see disabled jobs). We can also select all if we want to see all the jobs irrespective of the status.

Image 25: Filter the jobs

Click on Yes to see the enabled jobs and click OK.

Image 26: Filter condition in job

We can see only enabled jobs now based on the filter. The SQL Agent Extension also makes it easy to identify whether results are filtered out or not. It places a special symbol as shown below to identify that we have applied filter on this column.

Image 27: Filter icon in jobs

To remove the filter, click on the icon placed near the filtered column and select all.

Image 28: Select all to remove filter

We can see all the jobs now, but still you can see the filtered icon beside the Enabled column.

Image 29: Filter job icon in the column

In order to remove the filter icon, go back to filter option and click on Clear.

Image 30: Clear the filter conditions

We can now see the icon is gone for the filtered column.

Image 31: Verify the filtered icon

Delete a SQL Server Agent Job

If we want to remove a job from a SQL Server instance, right click on the particular job and click Delete Job. For example, let's remove Test Job from our instance.

Image 32: Delete job

This gives up the pop-up message to confirm deletion of the job operation or to Cancel it.

Image 33: Confirm to delete the job

Click on Delete Job and it removes the job immediately.

Image 34: Verify that job is deleted

In this tip, we learned about the SQL Server Agent extension and how to view, run, edit, and filter jobs from SQL Server Operations Studio.  In my next tip, we will explore creating jobs, operators, alerts, and proxies using the SQL Server Agent extension in SQL Operations Studio.

Next Steps


Last Updated: 2018-10-01


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools