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
30 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 ...
0
votes
0answers
23 views

How to install SQL Server Agent in existing SSMS [on hold]

I have been working on SSMS for Data mining, Now I would like to schedule a job. Where I am unable to find SQL Server Agent to schedule a job. Can any one help me how to get SQL server Agent into ...
2
votes
1answer
19 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 ...
4
votes
1answer
42 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
52 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
1answer
26 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
120 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 ...
1
vote
2answers
44 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
56 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 ...
0
votes
1answer
60 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
36 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
29 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
0answers
61 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
49 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
37 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
132 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
46 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
109 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 ...
0
votes
1answer
67 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
71 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
154 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
188 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
0answers
56 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

Hoping someone can shed some light. 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 ...
0
votes
0answers
53 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
447 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? ...
3
votes
1answer
92 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
152 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
152 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
74 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
112 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 ...
1
vote
1answer
80 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
31 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
64 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
88 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 ...
0
votes
1answer
86 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 ...
0
votes
0answers
73 views

Can I change “Category_Id” & “Current_execution_status” value in SQL Server 2012 through stored procedure?

I have created one job in SQL Server 2012 through SQL Server agent, and that job is not running due to some security privileges. When I am seeing through below SQL query to that my created job ...
1
vote
0answers
127 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
138 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
123 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 ...
4
votes
1answer
712 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 ...
1
vote
1answer
122 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 ...
0
votes
2answers
385 views

How to use sqlcmd to create file names for output based on the day of the week or the date?

I have a SQL agent job that queries the database and exports the results into a csv file. It needs to run daily and The output file (Output.csv) gets overwritten everytime the job runs. Is there a way ...
2
votes
3answers
134 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 ...
0
votes
0answers
107 views

Sql agent job fails with access denied error

We are trying to execute a SQL agent job which executes a step to shut down a service on DEV ENV using CMDexec, but it fails with error below: Executed as user: ABC\mssql. System error 5 has ...
1
vote
0answers
80 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 ...
0
votes
0answers
172 views

SQL Server Job Database name

I am trying to find and write a query for what database is affected by a SQL Server job. I have checked the following: Get a list of SQL Server Agent Jobs and SQL Server Agent Tables and have been ...
0
votes
2answers
28 views

can i secure sql server agent job with password

I want to secure SQL Server Agent jobs with password or any other method is it possible?
2
votes
1answer
282 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
1answer
445 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 ...
4
votes
2answers
200 views

Execute SP whenever any job is finished

I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP. Is there any simple way to do ...