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)

6
votes
1answer
144 views

SQL Server Agent initiating self-termination

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly: Sql Agent Output file [097] Memory leak detected [...
1
vote
0answers
7 views

Changing the Owner of Replication Jobs

Just wondering if there is any gotchas with changing the owner of a sql server replication job. The job owner is currently a domain account, but that person is leaving the company, so the account is ...
1
vote
1answer
216 views

msdb.dbo.sp_start_job got stuck in stored procedure

I have a stored procedure USP_A with logic like following: ...... BEGIN TRY exec dbo.usp_Log 'Start to run job job1' Exec @intErrorCode = msdb.dbo.sp_start_job 'job1' IF @intErrorCode <> 0 ...
0
votes
0answers
26 views

Run SQL Agent Jobs in Sequence

While there are other questions out there in forums, I notice that those are several years old, and I hope that things have changed by now. I am using SQL Server Management Studio 2014 and I have to ...
-3
votes
1answer
41 views

Upgraded to SQL Server 2012 and sql server agent scheduled jobs run twice

We recently upgraded from 2008 to 2012 and with one of our applications running on a ACTIVE & PASSIVE server (Always On) it has been running twice at the exact same time. We verified it wasnt ...
2
votes
1answer
120 views

Intermittent errors sending email from SQL Server Database Mail

I've been seeing some SMTP errors in my database mail logs. So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email ...
2
votes
0answers
46 views

SQL Agent parsing actually executes instead of just validating

I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident. This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory. I ...
17
votes
5answers
11k 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 ...
0
votes
1answer
231 views

Analysis Services job step error: The specified file could not be decrypted

We have a daily SQL Server Agent Job that's failing intermittently. Between May 27th and Jun 16th, it has failed twice. The error message we receive (listed below) references "The file could not ...
2
votes
1answer
651 views

Command fails as CmdExec job but succeeds from console

I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command: powershell -ExecutionPolicy Bypass -...
1
vote
1answer
33 views

Getting non owner to execute a job

I've attempted to implement solution three from the following Q & A: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it Table created and sproc created in msdb, inserted rows ...
4
votes
1answer
287 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

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 disabled. I can't figure out why ...
3
votes
2answers
4k views

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
0
votes
1answer
36 views

Getting SQL Server Agent to run multiple instance of a job at a time

As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time. I need it to Start a job 15 times, and then let them all run at the same time. Up to now, ...
0
votes
0answers
15 views

Identifying SQL Job name related to failing package message in SSISDB All Execution report [duplicate]

I have a situation that in SSISDB All Execution report I get an error message related to failing dtsx package. In link below I've found in here some basic information: SQL Server 2012 catalog....
-2
votes
1answer
40 views

SQL Server Agent job Executed as a weird login DOMAIN\ACCOUNT.file

I have a SQL Server 2008 SP3, I have an agent job that ran successfully for last the month, however as of yesterday it keeps failing error is below. Executed as user: Domain\Account. File '' ...
0
votes
0answers
17 views

Is it possible to configure exceptions for SQL Server Agent tasks?

I use SQL Agents tasks to execute my backup, maintenance & ETL tasks. IT would like to have a regular maintenance window to apply server patches. They're suggesting the 3rd Tuesday of every month ...
2
votes
3answers
15k views

How to move a SQL Agent Job to another Server?

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File ...
0
votes
0answers
42 views

SQL Server Agent job returns a failure status message after the job succeed.

We have a sql server agent job which runs every day on sql server 2008R2. The job has been running for years and had no issues before; and nothing has been changed recently. Two weeks ago, the job ...
0
votes
2answers
415 views

how to use trigger to make SQL Job's step success or fail

Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from table....
0
votes
0answers
116 views

Failed to execute IS server package because of error 0x80131904 … System.ComponentModel.Win32Exception: The handle is invalid

I have an SSIS package that is executed every 30 seconds by a SQL Agent Job. Every few days, an execution will fail with this message in the Job History: Failed to execute IS server package ...
2
votes
1answer
51 views

Prevent SQL Job Agent from interpreting a quotation mark as apostrophe

I'm struggling with my SQL Job Agent interpreting a quotation mark " as an apostrophe '. I have a T-SQL query which works in SQL Server Management Studio but not when I insert the exact same ...
8
votes
2answers
3k 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 ...
0
votes
1answer
63 views

T-SQL script doesn't delete files when run as an agent job - does if run manually

I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story - we're using a suppliers R1Soft for backup and it won't allow us to exclude ...
3
votes
2answers
138 views

What is considered for output from an sql server job step?

In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output? Background: Just executing a statement like UPDATE product SET ... will ...
1
vote
0answers
17 views

syspolicy_purge_history 3rd step runs forever on a cluster node?

I have a SQL Server cluster. On one of the nodes, the syspolicy_purge_history job runs forever on step 3. I did stop and restart the job, however, again it is running forever at step 3. How can I ...
0
votes
1answer
69 views

Identify SQL Error - Transaction count mismatching number of BEGIN and COMMIT statements

On SQL Server 2008, the sproc below runs as part of a sql agent job every 4 hours. Six times in the last two months, the error below has been logged in the agent job history: 04/07/2016 20:00:00,...
1
vote
1answer
62 views

SQL Server Agent Jobs - Long running job delaying other jobs?

I have several agent jobs that run on different schedules. Job 1 runs every 5 minutes, only executing a sproc that usually takes less than 30 seconds to run. This job Selects from table "users" ...
1
vote
1answer
32 views

Modifying the Contents of SQL Server Agent Alerts

As is generally recommended to do, I have Alerts configured for errors of severity 19 and greater that occur on my SQL Servers. However, as part of an email notification standardization process, I'm ...
6
votes
1answer
176 views

Principle of least privilege for sql server agent jobs

I've just read this article which advocates using the sa account for ownership of sql server agent jobs. http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account He has a couple ...
0
votes
2answers
87 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
2
votes
1answer
203 views

SQL Agent doesn't have perms to run a PowerShell script

I recently upgraded SQL Server from Windows 2008 R2 & SQL Server 2008 R2 to Windows 2012 R2 & SQL Server 2014 SP1. One of the sql agent jobs fails to run. The job runs a PowerShell script to ...
1
vote
0answers
103 views

Receiving historical SQL alerts when SQL Server agent is restarted

The issue is faced in SQL server 2014, whenever SQL Server Agent is restarted or if the server reboots, there are historical SQL alerts which are received and emails are being sent to the various DLs. ...
7
votes
2answers
2k 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 ...
1
vote
1answer
91 views

Start failed for Job in SQL Server Agent

I am trying to schedule a job which executes stored procedure but it is failing. DECLARE @return_value int EXEC @return_value = [dbo].[sp_WarehouseAttendance_New] SELECT 'Return Value' = @...
1
vote
0answers
176 views

No longer able to expand jobs in SQL Server Agent

In SSMS on a Windows 7 machine, while connecting to a server running SQL Server 2000, I am no longer able to click on the Jobs to see the Object Explorer Details. Previously, this was not an issue, ...
4
votes
3answers
121 views

Moving SQL Server Agent Job Categories and Schedules

I want to copy a set of Job Categories and Schedules between MS SQL Servers (2012/2014). select * FROM msdb.dbo.sysschedules Returns all schedules. select * FROM msdb.dbo.syscategories WHERE ...
10
votes
2answers
8k 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 date/...
4
votes
4answers
5k 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. I'...
1
vote
2answers
51 views

Checking Job Statuses As A Daily Task

I've been thinking about this a lot recently: Is there a consensus as to how to check SQL Server Agent jobs have run correctly? Some advice seems to be alerting the operator on failure and ...
1
vote
1answer
124 views

Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ. For context, there was an issue where the existing SQL Server backups had to be moved to ...
1
vote
0answers
58 views

Include failed step's error details in database mail for failed agent jobs

I receive database notification mail whenever a job fails. Error message of the failed job (Step Id=0 in the Job History) is included in the mail; MESSAGES: The job failed. The Job was invoked by ...
1
vote
1answer
48 views

File permissions for SQL Server 2014 virtual account

I have two SQL Server 2014 instances installed on the same server - one for production, and one for development. I have set up a SQL Agent job that identifies the most recent backup from the ...
2
votes
1answer
624 views

Replication monitoring refresher for distribution job affecting performance

I have a performance problem due to a sql agent job executing dbo.sp_replmonitorrefreshjob. The environment is a 2 node Cluster with SQL Server 2005 SP2 and with transaction replication. The OS ...
1
vote
0answers
73 views

SQL Server Agent Jobs starting but is idle

We have a data warehouse with a few databases. Every morning we import data from source systems to our data warehouse. The final step used to be to initiate loading of an Essbase cube by a SQL job ...
0
votes
0answers
17 views

How do enable the virtual account for my instance to start the SQL Agent

I get these errors when I try to use the virtual account to start the agent. I was using the built in account, NT Service, to start the SQL Agent but that began throwing errors daily after midnight.
4
votes
1answer
346 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 ...
5
votes
2answers
66 views

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

Why is sp_update_alert failing?

In SQL Server 2012, I have a Job Agent Alert set up as follows: EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert', @message_id=0, @severity=0, @enabled=1, @...
1
vote
1answer
352 views

Where to look for Agent history in Replication?

In SQL server 2008R2 replication configuration, "agent history cleanup distribution" job by default runs every 10 minutes and deletes any historical data older the 48hrs. If I increase the retention ...