Stack Exchange Network

Stack Exchange network consists of 175 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.

0
votes
0answers
14 views

Run two external processes in parallel using a table as a queue

I am currently making use of sp_execute_external_script to run either one R script or another based on parameters that are stored in a Jobs table. A custom application feeds data into that Jobs table. ...
0
votes
1answer
45 views

TSQL Agent jobs owned by users

I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job. I get tons of security findings about agent jobs owned by users. Most of these hits are system-type accounts (...
0
votes
0answers
14 views

Can target servers use master server job categories?

I'm using multiserver administration to deploy agent jobs to a group of target servers. I have a multiserver job category setup on the master and multiserver jobs are added to it. All looks good on ...
1
vote
0answers
23 views

AWS RDS SQL Server Native Backups

We have some AWS RDS SQL Server instances that we are setting up. Some of these will be Multi-AZ for HADR. We know that RDS will take system level backups and snapshots. We are wanting to perform ...
3
votes
5answers
98 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
55 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
33 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
65 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
2answers
114 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
78 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
39 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
31 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
30 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
104 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
30 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
42 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
125 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
37 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
52 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
19 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
105 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
36 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
84 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
140 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
102 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
599 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
78 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
101 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? ...
-3
votes
1answer
47 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
89 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
160 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
49 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
596 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
338 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
320 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
86 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
50 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
53 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 ...
3
votes
1answer
216 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 ...
1
vote
2answers
321 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
523 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
53 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
63 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? ...
1
vote
2answers
215 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
105 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
552 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
142 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
54 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
180 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 ...