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 ...

learn more… | top users | synonyms (1)

13
votes
3answers
7k views

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 ...
10
votes
2answers
5k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
8
votes
3answers
1k views

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 ...
8
votes
1answer
6k views

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 ...
7
votes
5answers
5k views

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

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 ...
7
votes
2answers
2k views

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name ...
7
votes
2answers
3k views

How do Availability Groups work with cheduled jobs and SSIS packages?

I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group. I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run ...
7
votes
1answer
555 views

What would be a good name for a job schedule?

I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive ...
6
votes
4answers
15k views

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 8. Now to schedule your newly created script. ...
6
votes
4answers
980 views

Run Server Agent Job on Schedule only if CPU is idle

I have a job set up in SQL Server Agent to run an update query. I want this job to execute every night between midnight and 5am and I want it to repeat every 5 minutes during that time frame. But I ...
6
votes
1answer
3k views

How to clear an Error 15404 after a DB restart (besides rebooting)

Every so often (e.g. ~months) a SQL Server Agent hourly Job will begin reporting an Error 15404 and continue to do so until intervened with. [298] SQLServer Error: 15404, Could not obtain ...
5
votes
2answers
3k views

Does the Sql Server Agent account need sysadmin permission?

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent ...
5
votes
2answers
1k views

SQL Server agent Error

I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7. Event Type: Error Event Source: SQLSERVERAGENT Event Category: Alert Engine Event ID: 318 Description: ...
5
votes
5answers
623 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
5
votes
2answers
3k views

SQL Server Agent not observing “execute as” permissions

I have a curious permissions problem. Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working. They previously worked; ...
5
votes
1answer
862 views

Execution-Policy Error running Powershell Script in SQL Server Agent

Running a powershell script from SQL Server Agent in 2014 using my AD account via a credential. I am getting the following error. A job step received an error at line 1 in a PowerShell script. The ...
5
votes
2answers
718 views

SQL Server 2000 - 'Performance: Deadlock'

We had to restart our SQL Server today, we had made no changes to it. When it came back up we immediately started getting this error from the server DATE/TIME: 2/27/2014 3:09:31 PM ...
4
votes
1answer
92 views

Scheduled stored procedure on all databases in my server

On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour. On the same server I have lot of databases with the same structure, and I'm constantly ...
4
votes
2answers
2k views

How do I rescue SQL Agent jobs from a set of detached master, model, mdb files?

I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself). I've SQL backups, and even ...
4
votes
5answers
2k views

Run Multiple Remote Jobs

I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job ...
4
votes
1answer
709 views

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 ...
4
votes
1answer
4k views

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 ...
4
votes
1answer
2k views

How to specify a task in a SQL Server maintenance plan conditionally?

I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp. These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans ...
4
votes
1answer
629 views

Renaming SQL Server Agent operator

I want to rename SQL Server Agent operator, that is change it's name. Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do ...
4
votes
2answers
543 views

Update strings for all cmdexec Jobs

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server? We'd like to change our logging location, and in principle I'd think this could be ...
4
votes
1answer
575 views

SQL Server Agent fails to start in Failover cluter

The SQL server agent in a cluster (as a cluster resource) fails to start. I'm looking for error logs to give me a clue, but so far, all it says is: "failed to start". What could be the possible ...
4
votes
2answers
2k views

Sql Server Agent notification email with custom message

I have set email notification on the completition of a scheduled job. Is it possible to customize the email content?
4
votes
4answers
21k views

How to succesfully run a batch file in an SQL Agent job?

I have a SQL Agent Job which generates a specific report in PDF-file and then copies the PDF to a network directory and then deletes the PDF file in the source directory. The SQL Jobs consists of 2 ...
4
votes
1answer
42 views

Passing current server name from SQL Server Agent job to PowerShell

I have a SQL Server Agent job that calls a PowerShell script in one step. And I want to pass the SQL server name and the current job-id to my script. With the job-id I have no problems. But with the ...
4
votes
2answers
917 views

SQL server agent SSIS error

I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't ...
4
votes
1answer
4k views

Agent job will not run

I created a JOB to run a simple package that runs a query and saves the results to an Excel file. If I try to run the job In SQL Management Studio I get an error saying: SQLServerAgent is not ...
4
votes
2answers
1k views

Powershell script executing within SQL Server Agent

I am trying to use Powershell within a SQL Server Agent job to download a zip file. The script uses PuTTY (PSCP.exe) to download a zip file from a SFTP site. The issue I am having is that when the ...
4
votes
2answers
200 views

Execute SP whenever any job is finished

I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP. Is there any simple way to do ...
4
votes
1answer
3k views

How do I get SQL Server agent to show up in SSMS for a non-sysadmin user?

I have already mapped the user to the msdb database and assigned him the role of SQLAgentOperatorRole, but the user still can't see the SQL Server Agent listed when he connects. The Agent show up fine ...
4
votes
1answer
56 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named ...
3
votes
2answers
2k views

SQL Server Agent - Run Query at the end of each month with date variable

I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM). Here is the query: INSERT INTO ...
3
votes
2answers
1k views

Execute a T-SQL job with a different connection string

Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string? The reason I want to ...
3
votes
2answers
7k views

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 ...
3
votes
2answers
2k views

Can I delegate SQL Agent rights without granting sysadmin rights?

I'd like to give a user the ability to administer SQL Agent jobs. According to the doc page, I can do this by granting the "appropriate" roles on msdb. I've granted all three SQLAgent% roles, but ...
3
votes
2answers
3k views

Get failed SQL Server agent job?

How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence? dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }
3
votes
3answers
4k views

Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields. I have a web interface that interacts with the sql scheduling system. ...
3
votes
3answers
132 views

Monitor SQLAgent

I detected that SQLAgent was not running on one of our servers due to a unexpected memory leak error that caused SQL Agent to shutdown. There were not alerts configured so one main job that was ...
3
votes
1answer
2k views

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

SQL Server 2008R2 PowerShell 2.1 I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I ...
3
votes
1answer
19k views

Access denied when trying to run an agent job

Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server. 12/03/2012 ...
3
votes
1answer
493 views

SQL Agent Permissions - Job Level

We currently have an SQL Job that runs an SSIS package. The output of the package is required for a report. As a result, the application that can create the report on demand first tries to run the ...
3
votes
3answers
966 views

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the agent to see if it is running or not, and ...
3
votes
2answers
12k views

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 ...
3
votes
1answer
533 views

SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps. This seemed to ...
3
votes
0answers
56 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

Hoping someone can shed some light. I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are ...
3
votes
1answer
91 views

Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?

(With apologies in advance since I've been asked to pick up a project written by someone with more skills than me but who I can no longer ping for questions. If it sounds like I may be using ...