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
vote
1answer
24 views

Working SQL script fails to create objects when run as SQL Agent job

As a crude 'integration test' style environment, I am updating a copy of production with a delta script based on "development vs prod". The tool used to create said delta script is free, and as such ...
0
votes
1answer
30 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 ...
2
votes
1answer
224 views

SQL Server Agent Notification Email if a Step has an error, but the job is succesfull?

Is it possible, that I can receive Email from Server Agent, if a step in an Agent has an error, but the Job is successful? In the history, I can see, that for example one Step has an Error, but all ...
4
votes
1answer
105 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 ...
6
votes
3answers
85 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 ...
1
vote
1answer
218 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 ...
0
votes
1answer
161 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 ...
1
vote
1answer
272 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
418 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 ...
2
votes
2answers
38 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 ...
0
votes
1answer
139 views

SQL Jobs Running - How to Kill

I ran the below query and it shows list of currently running jobs. However, in Activity Monitor none of these are running. How do I verify that these are/are not running? And how do I kill them? ...
1
vote
0answers
22 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 ...
0
votes
1answer
32 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 ...
4
votes
1answer
258 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 ...
0
votes
3answers
12k views

Cant see SQL Server Agent in SSMS [duplicate]

I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?
0
votes
0answers
15 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 ...
2
votes
2answers
1k views

How to see entire job step output?

We have a powershell job that creates a pretty long output (it has robocopy output, among other things). In the SQL Agent Job History, we only see the first 1k or so of this output. 1) Is there ...
0
votes
1answer
45 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
58 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 ...
0
votes
2answers
308 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 ...
5
votes
4answers
2k views

SQL agent job, why is the job history showing that a step is still running even though the job has completed

I have a sql agent job that runs Powershell as its first step (there are 3 steps in total). I have set this step to have 2 retries, with a 3 minute retry interval. When I look into the job history, ...
4
votes
1answer
2k views

How do I give file system access to SQL Server's 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 ...
0
votes
1answer
211 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 ...
3
votes
1answer
50 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 ...
4
votes
1answer
43 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 ...
2
votes
0answers
57 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 ...
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
31 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
28 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
1answer
178 views

Backup of Job's List only

Goal: Reuse the SQL server agent jobs list. Problem: The list contains a lot of jobs and I need to reuse the jobs list in a another time without recreating it. Based on the example of the picture, ...
0
votes
2answers
88 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
15 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)" ...
0
votes
1answer
55 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
44 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 ...
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 ...
11
votes
2answers
1k views

alert when any job in a job category fails

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails? I am wondering because I would like to set up an e-mail anytime an SSRS ...
7
votes
1answer
5k views

SQL Server Job History Disappears After 5 Days

I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being ...
1
vote
2answers
53 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 ...
3
votes
2answers
23k 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 ...
13
votes
1answer
392 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 ...
1
vote
1answer
156 views

SSIS unpivot returns different results when run via a SQL agent job

I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and ...
2
votes
1answer
64 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 ...
1
vote
1answer
836 views

SQL Server Agent - Running SSIS Package with Proxy

I have followed the below article to create credentials/proxy to run a SSIS job from SQL Server Agent. ...
0
votes
2answers
64 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
57 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 ...
2
votes
1answer
271 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
247 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: ...
5
votes
2answers
173 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 = ...
1
vote
0answers
49 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. ...
0
votes
0answers
24 views

Checking Job Status on Linked Server (MS SQL 2005)

We have 2 MS SQL 2005 Server instance. Both of them hold our warehouse environment. We receive around 100 files from our ERP System everyday and SQL Job on our Live environment Loads them in to ...