Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server-agent]

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 potential.

3
votes
5answers
74 views

How to stop a job for the rest of the day after the relevant condition has been satisfied once?

I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything. My problem is that once the IF condition is true, it ...
1
vote
0answers
36 views

Giving Access to only one SQL Agent Job

I will only need to allow a user to execute a specific sql agent job rather than be able to execute the other jobs that are there . Please advice how to accomplish this
0
votes
1answer
26 views

SQL Agent Job dependency

I have a requirement is SQL Agent Jobs where their is a job called Job 4 which dependent on 3 other jobs say Job 1, Job 2 and Job 3. So this Job 4 has to be triggered whenever either one of the 3 jobs ...
0
votes
0answers
40 views

how to collect the information provided by tokens in order to troubleshoot failing jobs quickly?

I was working on a way to take advantage of the SQL Server Agent Tokens and Macros because I work a lot with jobs and job steps and sometimes I have to notify different people depending on the outcome ...
0
votes
1answer
39 views

Rebuild index error: “The job was stopped prior to completion by User”

I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 ...
1
vote
1answer
41 views

How can I make SQL Server Agent job history available after failing over an AG?

I am implementing a new HA/DR architecture using Always On AGs for use in both a HA a DR scenario. I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled ...
0
votes
1answer
35 views

SQL Job Fails Logging In to Integration Services

I have a SQL Server 2012 where all databases are set up in instances. I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder. ...
0
votes
1answer
25 views

SQL Agent Jobs hanging at last Run date and time

For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done. When I enable the jobs via TSQL ...
0
votes
1answer
27 views

Sizing the SQL Agent Job Activity Monitor screen

Does anyone know how to reset the initial (default) window size that is used when opening the SQL server Agent Job Activity Monitor screen? When opening the Job Activity Monitor we always need to drag ...
1
vote
0answers
52 views

DB Restore Agent Job Fails After “..running the upgrade from version 695 to version 696.”

Recently I have started to see an intermittent error when trying to restore database 'X' via an agent job. The job runs a T-SQL script that restores a database from a 2008 R2 (production) instance to ...
0
votes
2answers
28 views

How to have SQL Server's SQL Agent pick up a new Powershell module

I have a Powershell module that I've written and uploaded to my SQL Server at C:\Program Files\WindowsPowerShell\Modules.I have a SQL Agent job with a powershell step that calls the module. This all ...
0
votes
1answer
39 views

SQL AlwaysON. keeping Agent Job in sync, but with same JobID?

Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists? I have a software from a vendor that uses the Job ID to ...
0
votes
2answers
55 views

SQL Server Agent - Do not show job step details and column headers in output file

I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file (Right click the job --> properties --> advanced and then adding a file path to the output ...
0
votes
1answer
34 views

How to secure SQL Agent Job that INSERTS to a linked server

What is the proper way secure a T-SQL agent job that moves data between linked servers? Setup: Microsoft SQL Server 2012 (SP3) Enterprise Edition SQL Server Agent running as NT Service\...
2
votes
1answer
44 views

how to set up sql server alerts based on a schedule basis?

We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.
1
vote
0answers
18 views

SQL Agent Job: Seeking Process flow advice on managing steps

Fellow DBAs, I need some practical advice on how to solve a job timing issue on different servers. I have a SQL Agent job that currently has steps 1-10. Step #8 has a dependency on a SQL Agent job on ...
1
vote
0answers
56 views

SQL Agent Powershell step not using specified proxy

I'm trying to execute a powershell script, and use a proxy to do. The job runs correctly, but using whoami within the script to log the executing user indicates that the script is not being run by the ...
1
vote
2answers
34 views

Get-SqlAgentJobHistory Invoked By

I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account. I'm running this cmd: Get-SqlAgentJobHistory -ServerInstance $...
1
vote
0answers
57 views

OLA Output File Cleanup - Better Options

Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
1
vote
0answers
71 views

SSIS job runs successfully but doesn't load data

I am running a job from SQL Server Agent and the job runs successfully. However it doesn't load any data (in my case Excel files from a local folder). I created a new Proxy Account to run this job but ...
0
votes
1answer
67 views

Why do we need to use proxies for syadmin job steps?

I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin. I just don't get why we need to use proxies even the ...
3
votes
2answers
503 views

Is there a way to back up agent jobs?

I can't find documentation about whether agent jobs in SQL Server can be backed up. If not, how can I back them up in order to restore during the future recovery in case of an instance failure?
2
votes
2answers
67 views

DBATools - pass through flat file configs to Agent Job Commands

I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite. Given $sa = Get-Credential and foo.config of ...
0
votes
1answer
63 views

Log shipping - Job schedules

When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules? ...
-4
votes
1answer
42 views

SQL Server Backup does not run

After changing the sa password of SQL server 2012, the automatic backup doesn't run any more. Please see the log history: The backup was not implemented by me. Login failed for user 'sa'. ...
0
votes
0answers
61 views

Is there any way to write to CloudWatch from an AWS RDS SQL Server Instance?

For scheduled tasks running against an AWS RDS server, I use a mix of apps living on an EC2 server and SQL Agent jobs. The apps can easily write success or failure to CloudWatch using the handy AWS ...
0
votes
1answer
117 views

CmdExec Powershell Job Step Escape Double Quotes

I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with ...
0
votes
1answer
46 views

SQL job ignoring IF condition

I have a job with a few steps(that run reports information) and i added a query i wrote to the last step. Sometimes the job fails because of factors i can not control (application issues) and my query ...
3
votes
1answer
342 views

INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache. As part of step 1 in the SQL job,i filter only the serial plans and insert it into a ...
0
votes
1answer
206 views

How to query Job Activity Monitor to get a list of all jobs

I am trying to obtain a list of ALL jobs (SQL Agent jobs). Specifically, I am trying to obtain the ID, name, status, LastRunOutcome, Last Run, Next Run, and most importantly description, owner AND ...
1
vote
0answers
244 views

SQL Server Agent stopped - Event ID 17052

I've had back to back Saturdays where our SQLServerAgent (Test Instance) has stopped with an Event ID of 17052. The times do differ as the initial week was 21:00 and last Saturday was 19:00. Having ...
1
vote
0answers
79 views

SQL Server Agent Alert does not pick up some of Events from Application Log written by SQL Server Audit

My end goal is to be emailed when new object (schema, table, procedure etc.) is created / altered / dropped at one of my databases on SQL Server I created a server-level audit, that writes to Windows ...
1
vote
1answer
45 views

Roles and Schduled jobs

I have some instances were some of the BI teams can create their own schduled jobs in the sql agent. However i also have a couple house keeping jobs i don't want them to interfere with. Can I create ...
0
votes
0answers
46 views

Is there a date modified timestamp for steps in a job on msdb?

I need to figure out which steps have been modified since the beginning of the month. I can find which jobs have been modified, but some of the jobs have 20+ steps, so knowing which specific step was ...
0
votes
0answers
135 views

Stored procedure runs fine manually but not as SQL agent job

I have a stored procedure that performs some data imports from a linked server. The linked server is on the same box as the main instance, it's just a different instance. The stored procedure runs ...
3
votes
1answer
179 views

SQL Server Agent Services not Coming Up

After the Windows Server 2008R2 was restarted after some planned downtime, the SQL Server Agent Service is not getting started. There are multiple SQL Server named instances and Agent Services of non ...
0
votes
0answers
9 views

When does a SQL Agent job split its output over multiple message rows?

If you look at the history of any given SQL Server Agent job that logs step output, you may see that it spreads this output over many rows. However, I can't seem to see any consistency as to when it ...
0
votes
0answers
93 views

Why does a scheduled SQL Server agent job fail when the proxy domain account is idle?

I have an SSIS package scheduled to run with an sql server agent using a proxy with my domain credentials. It imports data from a file dropped on a network drive that I have access to. The scheduled ...
1
vote
2answers
250 views

Trigger To Find Deleting Job Agent History

We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like: Create a trigger to check what application is ...
5
votes
2answers
392 views

SQL Server 2016 SQL Agent Token Not Working

I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..." But on my new SQL ...
0
votes
1answer
49 views

Archive backups with a PowerShell script launched by SQL Server Agent

I would like to run a Powershell script via the SQL Server Agent. This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a ...
0
votes
1answer
53 views

Job Activity Monitor - Log File Viewer is showing Duration for running jobs

As you can see in Log file Viewer it has a column Duration which change for a running job. I want to include this column in my query (from where I get jobname etc) Any idea to make a SIMPLE script? ...
0
votes
2answers
151 views

How do I automatically enable or disable report jobs at AOAG failover?

I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) ...
0
votes
0answers
85 views

SQL Job agent get running jobs, failed and canceld jobs in same query

I have 2 queries I want to combine into one, as below. I want to build on these queries due to restrictions I have as a user against the server. I can run exec sp_help_job @execution_status (it gives ...
2
votes
2answers
393 views

Domain Account or Windows account for Transactional Replication

All the online resources say to use a Windows account for Agent accounts, i.e. Snapshot, Distributor and Log Reader to set up Replication. See the following links as examples: Tutorial: Prepare SQL ...
0
votes
1answer
102 views

Multiple Agent Jobs Running same SSIS Package

I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example: 4 SQL Agent Jobs running the same SSIS in different schedules, I've ...
0
votes
1answer
52 views

SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently

I have a watchdog process that runs via a SQL Agent job to manage some custom tasks. One of the checks is that the process no longer exists: UPDATE R SET IsOrphaned = 1, DebugTimestampUtc = ...
0
votes
1answer
105 views

Job Agent sysjobhistory run_status=0 failure but which last step was a failure?

I am getting confusing results from sysJobHistory. The message can sometimes say last step was 2 but the row from instance_id says 0. Below are some examples below when run_status=0. Any idea how ...
1
vote
1answer
100 views

SQL Server Agent Service deleted

Has anybody run into the issue where an application (arcserv) using SQL Server Express 2008 is uninstalled on a server running SQL Server 2014? When arcserv was uninstalled, it or user deleted the SQL ...
1
vote
3answers
2k views

SQL Server Agent Server Login Account

What SQL Server login does the SQL server agent use to log in to the database instance? I am running the SQL server agent as the built in local system account. I have created a SQL agent job which ...