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)

-1
votes
0answers
41 views

SQL Server Job does not execute when scheduled, but does execute manually [on hold]

I am running SQL Server 2014 SP 1 CU 5. The job did work in the past. The schedule is enabled. The account it is running under is an ADS account which has sysadmin rights and has delete rights to the ...
0
votes
0answers
30 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
0answers
11 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
46 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 ...
3
votes
2answers
103 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 ...
0
votes
1answer
44 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 ...
1
vote
0answers
14 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
33 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 ...
1
vote
1answer
29 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 ...
1
vote
1answer
48 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" ...
6
votes
1answer
170 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 ...
1
vote
1answer
47 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' = ...
0
votes
2answers
57 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 ...
1
vote
2answers
39 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 ...
0
votes
1answer
57 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 ...
1
vote
1answer
70 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
46 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
40 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 ...
1
vote
0answers
61 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. ...
-2
votes
1answer
34 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 '' ...
5
votes
2answers
43 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
61 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, ...
0
votes
0answers
15 views

SQL Job No Longer Executes According to Schedule After an Error

I have many SQL jobs scheduled to run at various times. I've noticed that sometimes, not always, if the job aborts on an error, it will not execute its next scheduled execution. In fact, it won't ...
0
votes
1answer
33 views

Altering failed job email

I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process ...
1
vote
1answer
250 views

What does N'[Uncategorized (Local)] mean?

The following code is seen in several Q&A code snippets here (and elsewhere), I am trying to translate the section N'[Uncategorized (Local)] EXEC @ReturnCode = msdb.dbo.sp_add_category ...
6
votes
3answers
336 views

SQL Server Agent won't start

When I'm trying to start SQL Server Agent, I got this error: TITLE: Microsoft SQL Server Management Studio Unable to start service SQLAGENT$SQL_NAGA on server NAGA. (mscorlib) ADDITIONAL ...
2
votes
2answers
99 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
1
vote
0answers
33 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
5
votes
0answers
99 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 ...
0
votes
1answer
43 views

SQL Server Alerts (notify operator when job failed)

I setup database mail, created an operator today and enable notify operator when job failed. Is that all or do I need to create an alert too? My boss just needs emails if any job failed. Also how ...
0
votes
0answers
22 views

Replication jobs using proxy

I wanted to use a proxy account for replication agent checkup job on SQL Server 2012. This proxy works for other jobs. I got the following error: Executed as user: SQLUserID. Only members of the ...
0
votes
1answer
58 views

SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin

I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa. The security team decided to change that ...
4
votes
3answers
86 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 ...
3
votes
1answer
100 views

SQL Server - SQL Agent - Allow a Group to own a job

Scenario There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move ...
3
votes
1answer
44 views

How to handle Schedules when moving Jobs to another server?

We are migrating our SQL Server 2008 R2 to another physical server. For this, I will script out all jobs and run it on the target server. My question is about handling the schedules: When a job is ...
0
votes
0answers
16 views

Minimum permissions to modify a job owned by someone else [duplicate]

We have a development team that historically has managed it's own SQL Server jobs. We would like to continue to let them do this but historically they have had sysadmin and we don't want them to have ...
3
votes
1answer
40 views

Agent shows disabled(although it is not) after migration

I am migrating our old server to a more reliable and powerful new server. SQL Server edition and build number stays the same (SQL Server 2008 R2 Standard). For this I followed the steps given in this ...
2
votes
1answer
37 views

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

SQL Job Fails - Why?

This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails. (For security I have changed the server name and ...
1
vote
1answer
27 views

SQL Agent Job - Error Creating Step

I get this error when trying to create a Job Step: "Creating an instance of the COM component with CLSID from the IClassFactory failed due to the following error..(Microsoft.SqlServer.ManagedDTS)" ...
4
votes
1answer
263 views

Getting a proxy account to backup to a share using Ola Hallengrens backup script

I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts Engine - NT Service\MSSQLSERVER Agent - NT Service\SQLSERVERAGENT I am trying ...
0
votes
1answer
113 views

Is it possible to configure multiple database mail id in single account name in sql server 2012?

I would like to say that as i want to configure multiple database mail in single account name in sql server 2012. This is SQL-Cluster-DB , so as per direction from head, If any sql job will fail , ...
1
vote
0answers
52 views

SQL Job output as YYMMDD

I would like to ask how am I able to export my output file as YYMMDD.txt format in SQL Job? Right now i'm using SQL Token with $(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME)).csv My output now will ...
1
vote
2answers
64 views

SQL Server - cannot drop idle job

Currently, I am transitioning a server from a company to another. They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs. I'm ...
2
votes
0answers
105 views

SQL Agent - PSexec remote commands causing SSIS packages to hang

First post here, so please be kind! I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup. Each job is a multiple step ...
13
votes
1answer
851 views

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

SQL Server Agent logging actions and changes done to jobs

Is there a way to track changes done to SQL Server Agent jobs? If you had a reason to believe somebody is playing around with your job settings. How would you proceed to track who did it and from ...
0
votes
2answers
86 views

SQL Jobs logging only 2 days

When I righ click the job + view history, I can only see 2 days of logs. I'm sure this is a really noob question but I can't find the answer. I've been changing values but with no success. The same ...
0
votes
0answers
69 views

SQL Agent not Starting after Reboot Occasionally

I have a SQL server that runs fine most of the time, but about once every few months after a reboot (server reboots weekly) the agent fails to start with the following error: [000] SQLServerAgent ...