Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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.

Filter by
Sorted by
Tagged with
4
votes
1answer
174 views

Why the job's next_run_time is wrong?

I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017. ...
0
votes
0answers
23 views

SQL Server Agent runs a job on one server, not on the other

I have two SQL servers, each running on their own Windows server. The versions of both Windows and SQL Server are the same, they are on the same domain, they're virtually identical. On these servers,...
1
vote
1answer
25 views

SQL Server Agent - Report Failure but continue When intermediate step fails

I have a SQL Server Agent job that has three steps with the following control flow: Step 1 - on success - Go to next Step. on fail - job fails Step 2 - on success - Go to next Step. on fail - Go to ...
0
votes
0answers
15 views

Takes several hours to commit data on my data warehouse what seems for only one table

I have a data warehouse which runs several jobs during the night in order to update data. Background and job execution(s) setup One of these jobs updates the FactSale table consisting of the sales. ...
1
vote
1answer
32 views

SQL Agent job for SSIS execution fails with “An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed.”

A SQL Agent job with a SQL Server Integration Services Package step fails with the following message: Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility ...
0
votes
1answer
46 views

SQL Server: Description: The version of Destination - ***.csv is not compatible with this version of the DataFlow

I have downloaded SQL Server 2016 evaluation onto a Windows 10 laptop. I have imported some data into a new DB and new table. So far so good. I have exported the data to a CSV file and save this as ...
1
vote
0answers
26 views

SQL Server allow viewing of jobs by job category

I know how to assign or create "categories" for jobs. But is there a way to actually view jobs by category by default. I do not want to see all the jobs just listed within SQL Server Agent. I'd ...
0
votes
1answer
23 views

SQL Job PowerShell task fails same command succeeds as user

Simple requirement: delete files on remote path older than 20 days. PS C:\Users\Administrator> Get-ChildItem ` -Path \\FileServer\SQLBackups\SQLServer\ ` -File -Recurse -Force | Where-...
0
votes
0answers
44 views

Error 18456 when moving database to new environment

Last night I moved a database from Server Q (QA) to Server P (prod). Part of the process was to remove some QA logins we use and replace them with production logins. Things seemed to go well but ...
2
votes
1answer
74 views

SQL Server Agent Job is Delayed?

I have an SQL Server Agent job set up to run every minute on the minute. Usually it runs exactly at :00 on the clock but occasionally it waits several seconds. See below, one of the jobs was ...
1
vote
1answer
34 views

How to create a job that uses Powershell script? [duplicate]

I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have ...
2
votes
1answer
28 views

Allow two users permission to execute single agent job

I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the SQLAgentOperatorRole role in msdb as that will give them excess priveleges. I tried ...
0
votes
2answers
111 views

Schedule Full, Differential and Transactional Backup in SQL Server 2016

I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent). My first concern is, what is the best way to schedule my backups? I want full ...
4
votes
2answers
159 views

How can I run SQL Server Vulnerability Assessment from a SQL Job?

I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below. Invoke-...
3
votes
1answer
69 views

Is it normal for Snapshot Replication job to be constantly running?

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of ...
1
vote
1answer
25 views

Agent Job Running as Computer Domain Account Rather than Service account

I have two SQL Servers as follows Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64) Dec 27 2017 17:10:01 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
0
votes
1answer
61 views

Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When ...
0
votes
1answer
32 views

Newly created jobs need to be set for failure notifications automatically

Whenever a new / existing job gets failed on an instance of SQL Server, we should recieve an email about failure notification. Please help on how we can automate it?
0
votes
0answers
14 views

SSIS Jobs are getting hung

Job calling SSIS Packages are getting hung, When I restart the job, job shows running in but I can see below error in SQL agent Error log. Restarting SQL agent solves the issue. This happened ...
0
votes
1answer
92 views

SQL Server Agent Job - Unable to open Step output file. The step succeeded

I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to: $(ESCAPE_SQUOTE(SQLLOGDIR))\$(...
3
votes
0answers
61 views

SQL Server database partitioning purge job failed with an error

We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error: The ...
0
votes
0answers
60 views

SQL Agent PowerShell job runs in ISE but not in SQL Servere

My PowerShell script uses WinSCP to download files. It writes a message to a sql server table containing the number of files downloaded. When run within ISE it's fine. When run as a sql agent job ...
0
votes
0answers
12 views

Server State - SQL Agent Jobs

I have a user who has SQL Server State Permissions so he can monitor his databases and processes. However, there are others that are not the users dbs When a sql agent job failes on the other db's ...
0
votes
0answers
32 views

Automatic Query from root DB to new DB

I have access to an enterprise level DB used throughout the organization with tight constraints on who can access things. It contains almost all our data. I am a user of that DB with only read rights. ...
0
votes
1answer
69 views

SQL agent job for long running queries

I am trying to setup an alert where the application team can get the notification on their event viewer for any queries which are running longer than 2 minutes. This is what I have so far. For some ...
0
votes
1answer
91 views

T-SQL for starting Log Reader Agent

I am facing a problem regarding SQL Log Reader Agent. Whenever, I start SQL Server Agent, Log Reader Agent does not start. I have to start manually. Do I have start Log Reader Agent manually every ...
0
votes
0answers
18 views

Start Sql Agent Service with non-administrator priviliges

I'm trying to start SQL Server Agent service with domain account that is not part of administrator group. I found this https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms143504(v=...
0
votes
1answer
66 views

Red gate SQL Compare error while running using SQL Agent Job

I created a SQL agent job to perform a SQL Compare using the below command line powershell.exe -File D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.ps1 This below given is the command line code in DEVvsPRJ....
1
vote
1answer
58 views

Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts ...
4
votes
2answers
352 views

Start job from another SQL server instance

I have a job on an SQL 2008 server (Server A) (I know this is not ideal... That issue is being addressed). The agent runs under NT AUTHORITY\NETWORK SERVICE. I want to add a step that runs a job that ...
5
votes
2answers
279 views

What happens if agent job owner account is deactivated in Active directory?

We have a number of jobs owned by a DBA. The DBA is now leaving the company so their account will be deactivated in Active Directory. What affect will this have on the agent jobs?
2
votes
0answers
145 views

22046 “Impersonation error” running SQL Server Agent job

(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. ...
0
votes
1answer
63 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
21 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
47 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
384 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 ...
0
votes
1answer
93 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
128 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
334 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
184 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
57 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
44 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
44 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
233 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
97 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
58 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
391 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
44 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
94 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
20 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 ...