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.

Filter by
Sorted by
Tagged with
3
votes
2answers
2k 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 stored procedure that usually takes less than 30 seconds to run. This job Selects from table ...
6
votes
1answer
465 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
2k 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
1k 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 ...
4
votes
3answers
421 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 addressing ...
3
votes
1answer
1k 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
3k 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
349 views

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

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
181 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
326 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 ...
-1
votes
1answer
114 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 '' either ...
5
votes
2answers
2k 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
649 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
1answer
53 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
2k 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 @...
5
votes
5answers
19k 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 ...
4
votes
2answers
4k 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 ...
2
votes
0answers
333 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 ...
9
votes
1answer
555 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
478 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
1answer
803 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
2k 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
5k 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
89 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 ...
3
votes
1answer
642 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
5k 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
3k 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
298 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)" ...
5
votes
1answer
2k 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 ...
1
vote
1answer
2k 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
229 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 ...
5
votes
2answers
4k 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
648 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 ...
16
votes
1answer
9k 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 ...
5
votes
1answer
6k 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
1k 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 ...
2
votes
1answer
3k views

Restarting SQL Server Agent on a cluster installation

I've just enabled the mail profile in SQL Server Agent and I need to restart the service for it to take affect but the Agent is running as part of a SQL Server cluster and I don't want the cluster ...
1
vote
1answer
2k views

Cannot run Sql Server Agent jobs with PowerShell script

The problem: I have a customer that cannot run Sql Server Agent jobs with PowerShell script. It fails with this message: Message Executed as user: xxNC\SQLService. Unhandled Exception: System....
5
votes
2answers
321 views

Why does requesting the “estimated execution plan” for sp_delete_job throw an error?

I'm attempting to look at the estimated execution plan for the following T-SQL statement: EXEC msdb.dbo.sp_delete_job @job_id = '3A015189-F4EB-439B-9CA0-27AFB74719D8' , @originating_server = '(...
2
votes
0answers
574 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. ...
3
votes
1answer
6k 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
0answers
5k views

Where is the default location for the SQL Agent Job step output file? How do I update it?

I recently migrated my system databases to a new location. I then experienced failures of my SQL Agent jobs with the following error: 2015-10-03 01:53:10 - ! [136] Job - reported: Unable to ...
2
votes
1answer
2k 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
42 views

SQL Server 2005 Database Mail Resends Historic Messages following SP4 Build 5324

I have applied SP4 Build 5324 to a SQL Server 2005 Enterprise active/passive cluster (Build pathway: 4053 to 5000 to 5324). The SQL Server log contains zero errors. The SQL Server Agent log contains ...
7
votes
1answer
1k views

How do I ensure only one instance of a job is running?

SQL Server 2014 Standard Edition. We have a job that runs from 20-40 min. We only want one instance of this job running, but we don't want huge gaps between runs. So, scheduling it every 60 min is ...
1
vote
0answers
246 views

SQL Server agent error on a the mirror database

I configured mirroring on one of my databases. The database is in a SQL Server cluster. The mirroring is working great, but all the jobs that set to run fail. The error is: Date 9/10/2015 8:...
3
votes
1answer
125 views

SQL Server Agent logs in as guest on a linked server

I have a job called @test_credentials which runs the following query select * from openquery(SERVER2, 'select USER_NAME(),* from openquery(SERVER1,''SELECT USER_NAME() '')') and outputs those ...
3
votes
3answers
214 views

Finding out what permissions are required by the SQL Server Agent Jobs

I've been looking at this for a while and I can't work out an easy way to do this. As it stands every job in the database runs under SA level rights (horrifying thought) I'm sure some of the jobs ...
2
votes
1answer
181 views

Unable to change Agent Operators

I am trying to change a SQL Agent Operator in my system and it will not let me. I have sa but I think it's partially due to how I'm doing it. I'm trying to modify the operator with the name NIT and ...
6
votes
1answer
1k 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 ...