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
270 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 ...
2
votes
2answers
153 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
404 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
37 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
52 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
3answers
13k 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
30 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
2k 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
71 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 ...
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
3k 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 ...
3
votes
1answer
118 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
45 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
121 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
46 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
40 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
197 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
235 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
32 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
148 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
59 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 ...
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
73 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
26k 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
1k 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
174 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
154 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
939 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
97 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
73 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
555 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
429 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
187 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 = ...
0
votes
0answers
27 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 ...
1
vote
0answers
145 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 ...
4
votes
1answer
752 views

How SQL Server agent calculates next run date for a job

I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task. I have replicated sysschedules table for capturing users scheduling occurrences and ...
1
vote
0answers
31 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 ...
0
votes
0answers
151 views

SQL Server Agent (SHAREPOINT) cannot start

I am having trouble to start SQL Server Agent (SHAREPOINT). First it says SQLServerAgent must be able to connect to SQLServer as SysAdmin, but 'NT AUTHORITY\NETWORK SERVICE' is not a member of the ...
5
votes
1answer
269 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
66 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 ...
2
votes
0answers
2k views

SQL Agent After installation not able to start

SQL Server 2012 SQL Agent will not start after installation. Checked permissions, on surface configurations, all OK, have tried to run it as another account no Joy. The error i am receiving is the ...
3
votes
1answer
45 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
67 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
2answers
629 views

Sql Agent Jobs executes too slow

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** ...
2
votes
1answer
25 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 ...
5
votes
1answer
160 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 ...
-3
votes
1answer
1k views

SQL Server Agent Job Error running an SSIS Package

I've set up a SQL Server Agent job to run an SSIS package, but it is failing with an error: This Job failed. The Job was invoked by Schedule 1114. Does anyone know why I'm getting this error? ...
1
vote
1answer
209 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 ...