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)

2
votes
1answer
496 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
380 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
186 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
82 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. ...
2
votes
1answer
517 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 ...
0
votes
0answers
26 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
121 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
170 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
30 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
130 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
254 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
61 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 ...
3
votes
1answer
40 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
66 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
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
141 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 ...
0
votes
1answer
305 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? ...
2
votes
1answer
314 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 ...
2
votes
2answers
272 views

Why can't user with SQLAgentOperatorRole delete his own SQL jobs

I created a login and a user. I assigned the user as the db owner of the database "test". Then I granted him the role SQLAgentOperatorRole so that he can delete/create/modify the jobs belonging to the ...
2
votes
2answers
94 views

Automating an SQL task on a monthly basis

At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and ...
4
votes
1answer
73 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named ...
1
vote
1answer
201 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 ...
1
vote
2answers
68 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
1answer
48 views

inserting the table colomn data in multiple tables colomn.(SQL SERVER) [closed]

I want to insert one table colomn data into multiple tables colomns using triggers. For Example: I have one table name 'T1' having multiple colomns like 'C1','C2','C3' and now I want to insert these ...
0
votes
1answer
206 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
0answers
81 views

Good SQL Agent job wrapper… TRY/CATCH, Custom logging. (Review and provide input)

I'm looking for input on a small snippet of code i wrote. We use SQL Agent jobs to prepare big databases from production, before we ship the to test-environments. To get better feel for duration of ...
2
votes
1answer
100 views

Job is Storing History But 'Include Step Output in History' is not Checked

I have a SQL Server Agent Job. The 'Include step output in history' checkbox is unchecked. However, when I right-click oon the job and select 'View History', I can see that it is tracking step output. ...
-1
votes
1answer
329 views

Unable to start SQL Server Agent

We are unable to start SQL server Agent and it fails with below error: SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ...
1
vote
0answers
68 views

SQLServerAgent Error: 32

A job that "will Recycle SQL Server Agent Error Logs once every week on Sunday at 12:00:00 AM." has been failing this weekend on SQL Server 2008 R2 SP2 Enterprise. This job executes a system stored ...
1
vote
1answer
424 views

Creating a global temp table in a SQL Agent job

I want to create a global temp table in a SQL Server Agent Job. Here is the code: CREATE TABLE ##TestEmployee(Id int, EmployeeName nvarchar(50)) When I execute in SSMS, all is well. I then put ...
1
vote
1answer
284 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 ...
1
vote
0answers
168 views

SQL 2008 R2 Agent stopping sp_who2 running and can't view database or Agent properties

We're experiencing an unusual issue with SQL 2008 R2 SP2 (10.50.4297) where the SQL Agent had stopped but would not restart (this has been fine for months). After investigating the issue we had to ...
2
votes
2answers
366 views

SQL-Server login-error of SQL-Server Agent Account

I have to manage a configured SQL-Server version 2005 and have setup Database Mail successfully - test mail works. Also I've changed the agent service to a domain user with (currently) domain admin ...
0
votes
1answer
722 views

How do I pass or fail a job in SQL Server agent based on a return value from a stored procedure?

What do I need to do in order to execute a step in a SQL Server 2012 agent job that returns a bit value back? Basically, I want to fail the job or pass to the next step based on the results from the ...
3
votes
1answer
205 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 ...
0
votes
0answers
113 views

Incoming Tabular Data Stream Error

I created a SQL Server Agent Job with two different schedules. One is at 6 AM, the other is at 11 AM. When it runs at 11AM, it works fine; but at 6 AM it fails and gives this error message; 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? ...
4
votes
1answer
328 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 ...
2
votes
1answer
611 views

SSIS Packages get stuck in SQL Agent jobs [closed]

We run about a dozen scheduled SQL Agent jobs which run SSIS packages. Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's ...
2
votes
2answers
587 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
0answers
114 views

SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell

I have a SQL agent job set to execute a SQL query against a single database and then save each row into a different file with powershell before zipping the files up and emailing me. In one of my SQL ...
1
vote
1answer
197 views

Proper way to store a password within a SQL Server agent job

I know this is a stupid idea, but I'm having a hard time finding a better solution right now. I need to execute several sql stored procedures on a daily basis, then execute a powershell script to ...
1
vote
1answer
55 views

Why do you have to execute a full backup after a new database for trans backup jobs to run [closed]

Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). ...
0
votes
1answer
174 views

SSIS Package not retaining parameters in SQL Server agent job

I have an SSIS package stored in the Integration Services catalogs on a SQL server instance. This package is used as a step in a SQL Server agent job. The package has 4 required parameters. Using ...
0
votes
2answers
108 views

Executing sp in ssms much faster than executing in job step

My stored procedure takes 23 secs in ssms but takes 4 minutes when exec via job. Can you please help me in finding out. Below is SP code ALTER procedure [ops].[trailers_sitting_builder_mod] --WITH ...
1
vote
1answer
211 views

SQL Job to be run earlier on last work day of month

Right now, I have a SQL job (within SQL Server 2008 R2) that dumps a complex select statement into a table every weeknight (excluding weekends) and it works great. Due to our company's Month End ...
1
vote
0answers
266 views

How to check particular SQL UserID windows permissions in MS SQL SERVER 2012?

I would like to say as per my network system administrative security of windows Server 2012 R2. I am unable to start SQL Server agent. As per in MS SQL Server 2012 , i have "sysadmin" & "public" ...
1
vote
1answer
335 views

What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot

I have a SQL Server Agent Job creating a snapshot of my main database every minute: -- delete snapshot if it exists if exists (select * from sys.databases where name = 'mydb_snapshot' ) begin ...
0
votes
2answers
388 views

Start Sql Server agent when sql server starts

Is it possible to automatically start the agent when sql server starts? When you boot the server than both windows services get started. But when you stop the main sql server service agent also ...
5
votes
1answer
3k views

Get detailed output of SQL Agent job

We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose ...