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

569 questions
51
votes
8 answers

SQL Server Agent Jobs and Availability Groups

I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great…
nojetlag
  • 2,927
  • 9
  • 34
  • 42
29
votes
5 answers

Giving file system access to the SQLSERVERAGENT virtual account

I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in the control panel -> user accounts area. How do I…
Ryan
  • 503
  • 2
  • 5
  • 8
24
votes
7 answers

Could not find the Server Agent in SSMS

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8 Now to schedule your newly created script. In SSMS connect to the server you want to trace on.…
Jack
  • 2,557
  • 14
  • 37
  • 42
21
votes
7 answers

Script to see running jobs in SQL Server with Job Start Time

how to write script to see running jobs in SQL Server with Job Start Time? SELECT sj.name, sja.run_requested_date, CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration FROM msdb.dbo.sysjobactivity sja INNER…
zerbug
  • 329
  • 1
  • 2
  • 4
20
votes
4 answers

What is the Query to display the failed sql jobs

What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query
Avi
  • 317
  • 1
  • 3
  • 6
17
votes
4 answers

Get detailed output of SQL Agent job

We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose the problem. In the job history it states The job…
toryan
  • 335
  • 2
  • 3
  • 7
16
votes
3 answers

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in…
BBlake
  • 532
  • 2
  • 5
  • 11
16
votes
1 answer

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job is owned by a sysadmin (well, me) and run as the…
Mathieu Guindon
  • 914
  • 2
  • 7
  • 16
15
votes
3 answers

SQL Job step run as different sql account

I'm curious how to set up a SQL job step to be executed as a different SQL login account. It appears I need to set up a New Proxy Account which requires an existing credential. When I create a credential my only option is to use a Windows Login…
Geoff Dawdy
  • 1,143
  • 7
  • 23
  • 53
15
votes
3 answers

What is the best way to include SQL Server Agent jobs in Source Control?

I found that if I script a SQL Server Agent job as 'create to' that the script starts with use msdb- so I assume that jobs are stored in the msdb database? What is the best way to add SQL Server jobs to a Visual Studio database project? I added the…
Zach Smith
  • 2,430
  • 13
  • 34
  • 65
15
votes
4 answers

What's a good tool for visualizing SQL Agent jobs?

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a tool: A graphical summary of which jobs ran when,…
Jon of All Trades
  • 5,987
  • 7
  • 48
  • 63
13
votes
2 answers

Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some reason?
Kyle Brandt
  • 2,335
  • 9
  • 29
  • 37
13
votes
3 answers

I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?

Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case,…
SqlRyan
  • 1,206
  • 2
  • 17
  • 24
11
votes
1 answer

Does SQL Server 2014 Express, with Advanced Services, actually support full text search?

I've installed SQL Server 2014 Express edition, with advanced services. I was hoping to try out the full-text search features. I read here that full text search is supported by the 2014 express edition. But, after installing and trying to create a…
Ryan
  • 503
  • 2
  • 5
  • 8
11
votes
2 answers

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
Fulproof
  • 1,392
  • 2
  • 26
  • 36
1
2 3
…
37 38