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

2
votes
1answer
2k views

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

SQL Server 2008R2 PowerShell 2.1 I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I ...
2
votes
1answer
46 views

stored procedures vs inline SQL in a Job step SQL SERVER 2014

I have created a job with two steps. Step 1 deletes 50000 rows from an error table older than 90 days. Step 2 creates and emails a quick report on the number of new records created in the error ...
2
votes
3answers
82 views

Bulk change Agent Jobs' definition to “Quit the job reporting failure” from “Go to the next step” On Failure

There are agent jobs with multiple steps in our servers and all their steps are defined as "go to the next step" "On Failure". So, even a job step fails, operator doesn't get notified because ...
2
votes
2answers
1k views

Execute Remote Powershell script from SQL Agent

A test SQL Server job hangs when it executes a Powershell script. It does work using my login and a Powershell window, but not using the Agent account and a job. The SQL Agent user has ...
2
votes
1answer
852 views

Why do connections to my database fail, but only sometimes?

I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2. There is an application written in C#.net that connects to the database. Occasionally the ...
2
votes
1answer
1k views

Need Help Executing a Remote Task from SQL Server Agent

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
2
votes
1answer
931 views

SQL Server Maintenance Plan multiple database backups in a single folder

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day. For ex. I need to backup db1, db2, db3 and dump all these 3 ...
2
votes
1answer
482 views

SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps. This seemed to ...
2
votes
0answers
29 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 ...
2
votes
1answer
161 views

How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy

Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load ...
2
votes
0answers
82 views

Sql Powershell Script not working

I am performing a job from SQL server agent with a powershell command. It was running fine a couple of days back.. there is no change in the script. The script is also working fine if i run it via ...
2
votes
0answers
194 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. ...
2
votes
0answers
239 views

send custom email when SQL Server job fails

I have setup a profile and account for sending mail using DatabaseMail. I have a SQL JOB. If the job fails, I receive an Email Till this point, everything works fine. This is what the email looks ...
2
votes
1answer
1k views

The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

SQL Server 2008R2 is failing to execute log backup hourly. Date 5/2/2013 10:22:19 PM Log Job History (LOG BACKUP) Step ID 0 Server pc Job Name LOG BACKUP Step ...
2
votes
1answer
82 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 ...
1
vote
2answers
1k views

Trouble with Alerts and Notifications on SQL Server 2000

I'm trying to set up alerts and notifications in SQL Server 2000, but they are not working. I'm testing them for Agent scheduled jobs, and I get a note saying: NOTE: Failed to notify ...
1
vote
2answers
669 views

Specify a frequency range for a SQL agent schedule

I want to create a SQL agent schedule that occurs during a date range. I can't use "recurs every n weeks" because the process that it will fire off will change with each month. I need to set the ...
1
vote
1answer
29 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
4answers
4k views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE ...
1
vote
2answers
1k views

Disabling certain steps in a job

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first ...
1
vote
2answers
2k views

Run job once a minute, every day, except during a certain time

I have a Sql server 2012 agent job which currently runs every minute, every day, etc. I'd like to modify it so that it will run every minute, every day except on Saturdays between 7:00 pm and 11:30 ...
1
vote
1answer
89 views

SQL Server Agent

hi i need to run a procedure every first day of month. And the procedure should automatically run at every month.So with out using timer i have to use it. So i Think Better to go for SqlServer agent ...
1
vote
1answer
30 views

Cleaning disabled SQL agent jobs in SQL Server 2005 and above

For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working ...
1
vote
2answers
930 views

Maintenance Plan Job running at additional unscheduled times?

I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is ...
1
vote
2answers
646 views

How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?

Follow up to my question "What's the owner of SqlServerAgent job for?" Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs ...
1
vote
2answers
978 views

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
1
vote
1answer
48 views

How can I log the way in which a job was started?

Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database. The Job is run on a couple of ...
1
vote
1answer
154 views

How can I have a gap in the SQL Server Job Agent history for a job?

I have a job (Update Tickets) scheduled in Job Agent with a single schedule of "Occurs every day every 30 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 11/4/2013." ...
1
vote
3answers
6k views

How to move a SQL Agent Job to another Server?

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File ...
1
vote
1answer
1k views

How to launch SQL Server Agent job on completion of another one?

An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs. Is there any way to start SSA jobs on completion (or success, or failure) of another one? Currently, I do this on ...
1
vote
1answer
628 views

How can I change SQL Agent Log file to use unicode format?

I've created a database with tables that heavily leverages nvarchar for most of the columns. One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the ...
1
vote
1answer
33 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
44 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 ...
1
vote
2answers
1k views

Unable to connect to FTP server using “FTP Connection Manager”

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log ...
1
vote
1answer
70 views

Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not

I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager. The BoL/MSDN says that this user ...
1
vote
1answer
3k views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
1answer
203 views

How do I find out if Sql server Agent's login has rights to a DB?

How do I find out if the SQL server Agent's login has rights to the target DB, that is the DB on which I will be executing a query ? Also, how do I find the service that's controlling Agent on the ...
1
vote
1answer
606 views

Run a program on a remote computer after a SQL job has completed

We have a SQL Agent Job that performs a few tasks every night (schedulled to run using SQL Agent). The database server is SQL 2012 if that makes a difference. We have another process (a .net program) ...
1
vote
1answer
253 views

SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a sproc no longer existing)

First of all apologies for the novel but there's a lot to explain... We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be ...
1
vote
1answer
550 views

Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package

I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on ...
1
vote
0answers
31 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 ...
1
vote
1answer
15 views

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

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). ...
1
vote
0answers
59 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
84 views

SQL Server : Job not reconnecting to restarted Linked Server

The problem To keep two tables across two servers in sync, we have a trigger that adds a change to a MergeTable. Every so often, a merging job runs on SQL Server to push the changes to the Progress ...
1
vote
0answers
46 views

Sql agent job step to Rename a file in SQL Server 2000 fails with error “ file being used by another process”

We have a SQL agent job configured on Sql Server 2000, which has an initial step of renaming a backupfile coming from a another server (SQl Server 2000) Say coming from \A\B\X.dat to X_back.dat. till ...
1
vote
0answers
264 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 ...
1
vote
0answers
58 views

Failed to notify 'DBA Team' via email

I setup a test job to test out my failure notifications and now I'm getting the following stating it did not notify my operator via email. The strange thing is I ran the same exact setup script on ...
1
vote
0answers
25 views

Is it possible for a user that is not the job owner to edit the job or add new steps?

My development lead is ok to revoke the sysadmin privileges he currently has, if there is a way he can still edit/execute/enable-disable/execute application jobs. So far, I see that with the ...
1
vote
0answers
106 views

What are recommended (working) permissions for a user to view all SQL Agent jobs

A new user (associated with an existing login) is created in msdb and assigned membership: use [msdb]; create user [TheUser] for login [AnExistingLogin]; alter role [SQLAgentUserRole] add member ...
1
vote
1answer
503 views

Agent Xps getting disabled automatically

In one of SQL 2012 server, agent Xps is getting disabled automatically. There is no set pattern for the same and the issue started all of a sudden. Through profiler it was found that application named ...