Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sql-server-agent]

SQL Server Agent is a Windows service installed with the SQL Server Standard Edition and above. It provides an automated way of executing or performing administrative task against a SQL Server instance. SQL Server Agent is a tool that every DBA should become familiar with and utilize to its full potential.

SQL Server Agent is a Windows service that is installed with the SQL Server Database component of SQL Server. It is available in Standard Edition and above. Please note that it is not included with the SQL Server Express nor is it supported (currently) with SQL Server Azure. It is made up of various components that include the following items below. Also note that you can configure all the components of SQL Server Agent through SQL Server Management Studio (GUI) or by utilizing the SQL Server Agent stored procedures.

Jobs
A job is made up of actions or commands that you want SQL Server Agent to perform. These actions can be divided up into steps and create a unique sequence of events. If you utilize SQL Server Maintenance Plans these are created as SQL Server Agent Jobs.

Schedules
A Schedule is configured for each job to automate when the job executes. You can configure a schedule to be run once, on a recurring basis (daily, weekly, etc.), or even when the CPU is idle. A job can contain more than one schedule.

Alerts
An alert is configured to fire off an automatic response to a certain event. You can configure an alert on one of three events: SQL Server event (e.g. error code), SQL Server Performance Condition (perfmon counters specific to SQL Server), or WMI events (utilizing WMI queries, specific to the local server).

Operators
An operator provides the ability for SQL Server Agent to notify an individual or group of individuals based on email or pager (through email). Net send is also an option however it is on the depreciation list as of SQL Server 2012. In order to use the notification you must configure the SQL Server Agent Alert System with a Database Mail profile.

Questions related to this subject are tagged: sql-server-agent

You can learn more on SQL Server Agent through the links below:
SQL Server Agent
SQL Server Agent Components
SQL Server Agent Stored Procedures
SQL Server Agent Security
SQL Server Agent F1 Help Configure SQL Server Agent to send job status and alert notifications

542 questions
6
votes
3 answers

Scheduling jobs on a SQL Always On HA cluster

When scheduling jobs using SQL Server Agent for AOHA cluster, does one use a Master-Target config as you would as if the servers were separate? Or is there a better way to distribute the jobs to all of the servers so that they can be picked up by…
Rob Hutton
  • 61
  • 1
  • 4
3
votes
2 answers

SQL Agent Jobs - What Permissions Required

We have a application that allows a user to view a list of SQL Agent jobs and create new ones. However, the application displays a form asking for user credentials. The user is a member of the SQLAgentOperatorRole in msdb. I tested by making the…
K09
  • 1,394
  • 13
  • 36
  • 59
1
vote
1 answer

Copy files using script to a network share

Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups. Thanks
medina
  • 197
  • 1
  • 2
  • 7
1
vote
1 answer

Role or permission to view but not create SQL Agent jobs

The Microsoft SQL Server database role SQLAgentReaderRole apparently lets a member create SQL Agent jobs along with viewing jobs, job steps, and results. We need to allow a user to see all SQL agent jobs, steps, schedules, and results, but not…
DWalker07
  • 131
  • 4
0
votes
1 answer

Run job in agent based on table data

Is there a way to start a SQL Server agent job if a table is not empty? Basically, first I want to check a table, and if the table is not empty then start the job. If it is empty, then check every 1 hour to see whether it has the required…
user20907
  • 173
  • 2
  • 3
  • 11
0
votes
0 answers

emails sent by a phantom job: find a sender job

Every night at the same time I receive some mails like that: DESCRIPTION: Error: 50000 Severity: 16 State: 1 [ERROR]:DWHDumpProcess_XXX job is either failed or not in good state. Please check. Return Code (null) sent by different DWH SQL…
Mainecoon21
  • 171
  • 8
0
votes
0 answers

Server State - SQL Agent Jobs

I have a user who has SQL Server State Permissions so he can monitor his databases and processes. However, there are others that are not the users dbs When a sql agent job failes on the other db's he gets notified. Can I stop this and if so,…
taslim shaikh
  • 1
0
votes
1 answer

SQL Agent Jobs hanging at last Run date and time

For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done. When I enable the jobs via TSQL it seems the schedule hangs (at last run date)…
CherryDz
  • 37
  • 2
0
votes
1 answer

when scripting SQL server agent jobs, is it possible to use @@servername in the @command settings line

I am trying to script an SQL server agent job so that one script can be run on multiple servers to set up the same job. we have the same users and will use the same job name on the different servers. however there is one part that I am struggling…
matt
  • 3
  • 1