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:
- What is the SQL Server Agent?
- Is the SQL Server Agent included in SQL Server Express Edition?
- How can I schedule tasks in SQL Server Express Edition?
- How can I start or restart the SQL Agent service?
- How can I create a simple job to backup my database every day at 9 pm?
- How can I create a job that executes the command line (cmd)?
- How can I check if a job fails?
- When I execute the command in cmd it works fine, but if I run in the agent it fails. What can be the problem?
- Is it possible to run jobs across multiple SQL Servers?
- How can we create alerts using the SQL Agent?
- How can we send emails using the SQL Agent?
- 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:
|
1 |
BACKUP DATABASE [testdb] TO DISK = N'C:\sql\test.bak' |
Next, we will create a file named backup.bat to invoke the script in sqlcmd:
|
1 2 |
sqlcmd -S ServerName\SQLEXPRESS -E -i c:\sql\backup.sql -o c:\sql\output.txt |
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:
|
1 |
NET START SQLSERVERAGENT |
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:
|
1 |
BACKUP DATABASE [testdb] TO DISK = N'C:\sql\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:
|
1 |
net user japex mypwd /ADD |
This command creates a user named japex with password mypwd.
In a next step, you can grant sysadmin privileges to the user japex:
|
1 2 3 |
CREATE LOGIN [MYSERVER\japex] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYSERVER \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:
|
1 |
Select * from dbo sys.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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023