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
0
votes
0answers
11 views

gMSA for running a sql agent job

I've had the question if it's possible to add a gMSA-account to the SQL-logins in order to let it perform a certain SQL Agent Job. I know it's possible to add a gMSA as a login but it will probably ...
2
votes
0answers
35 views

SQL Server 2016 Agent jobs start, but hang indefinitely on first run after a server bounce

I have multiple SQL Server Agent jobs that hang indefinitely upon first run after the server bounces. The jobs execute as scheduled, but never perform any action and stay in "Executing" status ...
-1
votes
1answer
31 views

SQL Job runs without it schedule and renaming or deleting it causes other jobs fail

I recently migrated the SQL 2008 to SQL Server 2016. I started noticing a strange issue where one of the SQL Job is causing the failure of other jobs. This job is disabled and there is no schedule and ...
0
votes
1answer
17 views

Granting access to users to run specific SQL jobs that runs SSIS packages [duplicate]

We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?
0
votes
1answer
32 views

Datepart logic for custom sql agent job schedule

Wanted help in code for below logic. Job A needs to be run on 8th of month. Job B needs to be run on Tuesday only. Job c needs to be run on Daily(Minus Tuesday & Sunday). And If Its Tuesday and ...
-2
votes
1answer
29 views

Using Ola Hallengren's backup solution - Can't exclude databases [closed]

I am using the newest version of Ola's scripts. When backing-up databases, I'm experiencing an issue with excluding certain databases. The website states, if you want to backup all User database ...
0
votes
0answers
44 views

Error while executing PowerShell script using SQL Server Agent Job

I am executing a PowerShell script using SQL Server Agent job, script code is given below(it is added as is in the Job step 1) : $OriginFile = "\\vmpr-file1\share3\RT-BI_Shared\ABC.xlsx" $SourceFile =...
0
votes
0answers
49 views

CDC capture Job Step-2 is not running when enabling CDC on table in SQL Server and Change table is empty

My Database has CDC enabled and the table i want to track changes using the below T-SQL, EXEC sys.sp_cdc_enable_db; GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'...
0
votes
1answer
40 views

How to copy a SQL Agent Job on same Server?

I want to make a few duplicate copies of an existing SQL Server Agent Job on the same server and edit them to do the same action on different databases that exist on the same server. These are ...
0
votes
1answer
27 views

What permissions does the SQL Agent's user need to execute Powershell scripts?

I'm trying to create a job with a Powershell step. To recreate this: create new job, give it a name add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test" set the ...
0
votes
1answer
14 views

SQL Server Agent Job Next Run DateTime Stuck In The Past

The job is enabled, the schedule is enabled, the agent service is running, but the job is not running as it is supposed to. Looking in the Job Activity Monitor, it shows a Next Run that is actually in ...
0
votes
0answers
12 views

SQL Server and Agent Services not starting automatic in AZURE IaaS

We migrated an on-premise database to azure IaaS and the machines is on between 7am and 7pm that way we save money in the night when the users are not querying the database, but every time the machine ...
0
votes
0answers
28 views

Sql Agent Job: Behavior different than running on command line: starts new process vs. existing process

I'm using sql server agent job (sql server 2019 cu1) to execute a function in a third-party running program. From the command line it is simple: myProg.exe -myParam This will always activate the ...
0
votes
2answers
171 views

Execute sp_update_jobstep to change SQL Agent job step type

As I was going to deploy a change to our Ola Hallengren indexing scripts, I noticed on some SQL Servers we have indexing job running as an "Operating System (CmdExec)" step instead of being called as ...
1
vote
2answers
111 views

Is it better to have one instance of a continuously running agent job or to fire off the job multiple times throughout the day?

I have a SQL query I need to run every second for about 12 hours a day. It's a pretty simple query that logs some data to an isolated table. Right now I've been leaving it running in my local SSMS ...
2
votes
0answers
44 views

SQL Server Agent is not ending some jobs correctly

we observed a strange behaviour after migrating our datawarehouse from SQL Server 2012 to SQL Server 2019. Our Agent runs most of the jobs correctly after the migration. However there are a few were ...
0
votes
0answers
54 views

SQL Job get stuck on a simple Select into vaiables query

I have an issue that I please need help in trying to resolve as its really becoming a bit pain. I have some Jobs that monitor our systems for calls stuck and it's getting stuck (the irony is not lost ...
10
votes
3answers
2k views

SQL Server Agent fails after installing SQL Server 2019 CU2

After installing CU2 our SQL server agent randomly stops working. If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error: ...
0
votes
0answers
25 views

SQL Server Agent fails to start

I am replacing a SQL Server 2012 cluster with a new one (keeping the same version of SQL, changing OS from 2008R2 to 2012R2). I have 1 node installed so far, but the agent won't start. The error ...
0
votes
1answer
89 views

SQL Agent using domain user for Jobs with TSQL

I have tried every option but i still cant find out why my sql agent is using a domain user when executing job steps with TSQL and keep getting the following error: Executed as user: RESOURCE\...
1
vote
1answer
312 views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
1
vote
2answers
63 views

SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database

I have SQL Server 2014 and I'm using SQL Server Agent to start some scheduled procedures during the day. These procedures make some selects (in DBlink) on an Oracle database that is handled by another ...
4
votes
1answer
230 views

Change character limit of SQL Agent Operators

I want to add more than 100 characters worth of email addresses to the SQL Agent Operators alerting. For example: emaione@example.com;emailtwo@example.com;emailthree@example.com;.... I tried to get ...
0
votes
1answer
34 views

Why does an SQL Server Agent job try to create a procedure

I am using SQL Agent to schedule the exeuction of a procedure. The first time that the procedure is executed, it runs without problems. The second time the job (step) executes, it does not finish. A ...
0
votes
1answer
131 views

SQL Server 2012 Agent hangs at a particular time of the day

There is a 3 node SQL Server Failover Cluster with 3 instances running on same node at the moment. SQL Server version is 2012 with SP3. At a particular time of the day at 4 PM daily, SQL Agent hangs ...
3
votes
1answer
290 views

SQL Server job stuck even after restart

I have a SQL Server Agent job stuck in "In Progress". It's a FULL backup that usually takes 30 minutes, but it's taking 7 hours and counting. I've restarted the Agent and then the whole OS and it ...
1
vote
2answers
79 views

Find jobs that succeeded, but had steps that failed

Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows? I'm new to this side of development and I'm not sure about all of the system tables, etc. If ...
1
vote
0answers
193 views

SQL Server Agent log is showing error [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite)

While checking SQL Server Agent Log i found lot of error like follows [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite) [298] SQLServer Error: 10004, ...
1
vote
0answers
66 views

Why is failed job not logged in SQL Server Agent Error Log?

I have a failed job which is logged in SQL Server Logs but not logged in SQL Server Agent Error Logs. The job is executed as user: NT SERVICE\SQLSERVERAGENT. Limit size of job history and Remove agent ...
4
votes
1answer
259 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
123 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
34 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
22 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
358 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 ...
1
vote
0answers
64 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
110 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
112 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
43 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
70 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 ...
2
votes
2answers
491 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
293 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
366 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
34 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
280 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
35 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
289 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
122 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
127 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 ...

1
2 3 4 5
10