Daniel Calbimonte

FAQ and examples about the SQL Server Agent

May 11, 2018 by

Introduction

In this article, we will answer FAQs about the SQL Server Agent. We will learn how to create a job, some things about the internal tables used, how to schedule jobs, add PowerShell jobs, cmd jobs, T-SQL jobs and more.

In this article, we will answer the following questions:

  1. What is the SQL Server Agent?
  2. Is the SQL Server Agent included in SQL Server Express Edition?
  3. How can I schedule tasks in SQL Server Express Edition?
  4. How can I start or restart the SQL Agent service?
  5. How can I create a simple job to backup my database every day at 9 pm?
  6. How can I create a job that executes the command line (cmd)?
  7. How can I check if a job fails?
  8. When I execute the command in cmd it works fine, but if I run in the agent it fails. What can be the problem?
  9. Is it possible to run jobs across multiple SQL Servers?
  10. How can we create alerts using the SQL Agent?
  11. How can we send emails using the SQL Agent?
  12. Where is the SQL Agent information stored?

Requirements

First, we will have SQL Server Installed. In this example, I am using the Developer Edition (SQL Server Express edition does not include the SQL Agent).

Getting started

What is the SQL Server Agent?

It is a component of the SQL Server that allows to schedule and program jobs to automate some tasks in SQL Server.

Is the SQL Server Agent included in SQL Server Express Edition?

No. SQL Server Express Edition is a free version that does not include the SQL Agent (because it is free).

How can I schedule tasks in SQL Server Express Edition?

You could use the Task Scheduler included in Windows and invoke a batch file with an invocation to the sqlcmd with the command required.

The following example is a batch file that creates a backup to a SQL Server database:

First, create a script named backup.sql file with the backup command:

Next, we will create a file named backup.bat to invoke the script in sqlcmd:

Where sqlcmd is the command line and -S is used to specify the SQL Server Instance name, -E is used to connect using the current Windows Account and -i is used to specifying the input which is the script backup and -o is used to show the results of the backup in a file named output.txt. In addition, you will need to invoke the backup.bat in windows scheduler.

How can I start or restart the SQL Agent service?

You can start the SQL Agent Service using the SQL Management Studio:

Also using the SQL Server Configuration Manager:

You can also use the command line using the following command:

How can I create a simple job to backup my database every day at 9 pm?

First, you need to create a new job in the SQL Server Agent and enter a name and optionally a description. Then go the Steps page:

In steps, create a new step and add the following T-SQL command to backup the database named testdb in the file test.bak:


Go to schedules page and press the new button:

Specify any name for the schedule and in schedule type, select recurring and set it to run daily and at 21:00. Now you have a backup ready to run daily at 9:00 PM.

How can I create a job that executes the command line (cmd)?

When you create a new job and a new step (see the previous question if you need detailed steps) you can invoke the Windows command line (cmd). The following options show how to create a local Windows User and then in a second step we will grant permissions to the database in a second T-SQL Step:

In a job step run this command:

This command creates a user named japex with password mypwd.

In a next step, you can grant sysadmin privileges to the user japex:


How can I check if a job fails?

You can right-click the job and check the view history to check when it failed and why it failed:

You can verify the time and reasons:

When I execute the command in cmd it works fine, but if I run the command in the agent it fails. What can be the problem?

A typical problem is the permissions problems. If you have an access denied error in your SQL Agent job, you may need more privileges to run the job.

By default, the SQL Agent runs with the SQLSERVERAGENT account. This account does not have administrator privileges it does not have permissions in some folders and other Windows objects:

A quick solution is going to the SQL Server Configuration Manager and modifying the account to an administrator or grant privileges to the SQLSERVERAGENT account.

However, it is not recommended for security reasons, to use an administrator account to the SQL Server Agent because a hacker or someone could use that account to attack your OS and your SQL Server.

A good practice is to create a Proxy. First, you will need to create a Credential:

Specify administrator credentials:

Now, go to the SQL Server Agent, Proxies and right click on Operating System (CmdExcec) and select New Proxy. We will add a proxy to execute cmd tasks with more privileges:

Also, enter a name and select the credential just created:

Finally, in your job step run as the proxy just created.

Is it possible to run jobs across multiple SQL Servers?

Yes, the following article shows how to run jobs on multiple servers:

How can we create alerts using the SQL Agent?

The following article shows how to create

How can we send emails using the SQL Agent?

The following article show how to work with emails:

Where is the SQL Agent information stored?

All the information is stored in the MSDB database. This is a system database that stores the jobs, steps, operators and all the information related.

The following query shows how to get all the jobs:

For more information about the msdb database, refer to this link:

SQL Server system databases – the msdb database

Conclusion

To conclude, we can say that the SQL Agent helps a lot to automate different tasks and it has a lot of functionality.

See more

To manage SQL Server Agent jobs across multiple servers, consider ApexSQL Job, a tool specifically designed for SQL Server DBAs

References


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
Maintenance

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

1,044 Views