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.
542
questions
0
votes
1
answer
120
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 ...
0
votes
1
answer
635
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
0
answers
815
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 =...
-2
votes
1
answer
725
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 ...
8
votes
1
answer
9k
views
SQL Server alerts not sending notification emails to the operator
I have setup few SQL Server alerts and added Operator to notify if any alert occurred.
I just noticed that couple of alerts did occurred but we didn't receive any alert email. I did send a test ...
1
vote
1
answer
5k
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
1
answer
733
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
0
answers
107
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 ...
11
votes
3
answers
3k
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
0
answers
295
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
2
answers
7k
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
2
answers
666
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
0
answers
257
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
0
answers
705
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 ...
6
votes
3
answers
5k
views
Scheduling jobs on a SQL Always On HA cluster
When scheduling jobs using SQL Server Agent for AOHA cluster, does one use a Master-Target config as you would as if the servers were separate? Or is there a better way to distribute the jobs to all ...
0
votes
0
answers
411
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
1
answer
2k
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\...
4
votes
1
answer
2k
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: [email protected];[email protected];[email protected];....
I tried to get ...
0
votes
1
answer
96
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 ...
3
votes
1
answer
4k
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
2
answers
876
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 ...
2
votes
2
answers
3k
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 ...
1
vote
0
answers
1k
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
votes
1
answer
739
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
0
answers
441
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 ...
0
votes
0
answers
404
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,...
4
votes
1
answer
2k
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.
...
2
votes
1
answer
1k
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 ...
2
votes
2
answers
20k
views
SQL Server Agent Error 1053
A few months back I installed SQL Server 2008R2. At the time of installation I used
1.Mixed Authentication
2.NT Authority\NetworkService as the SQL Agent user name, and the same user name for all ...
2
votes
1
answer
5k
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 ...
7
votes
3
answers
12k
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?
1
vote
2
answers
2k
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
0
answers
57
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 ...
1
vote
1
answer
160
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 ...
7
votes
3
answers
104k
views
Execute Sql statement and send result to email
I have to execute SQL statement every day and send result to dba by email.I configured database email.
Below is query:
SELECT
account.accountID,
account.name
FROM
...
4
votes
1
answer
2k
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 ...
2
votes
1
answer
734
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 ...
4
votes
2
answers
1k
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-...
1
vote
1
answer
2k
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 ...
1
vote
1
answer
465
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
1
answer
85
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
1
answer
1k
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))\$(...
1
vote
1
answer
250
views
SQL Server 2016 Cluster - mail works in alerts; sp_send_dbmail dosen't send in jobs
I upgraded from 2008 R2 to a 2016 cluster [Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)]. We have some scheduled emails that happen from the agent that are reporting success as the ...
0
votes
3
answers
6k
views
Remote execution of SQL Agent job
Working on setting an automated restore of dbs from Production to the test/dev environments using Net App SQL Snap Manager. The prod job handles the kick off of the restore, nothing being ran on the ...
3
votes
0
answers
284
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 ...
13
votes
3
answers
4k
views
I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?
Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as ...
3
votes
2
answers
2k
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 ...
6
votes
5
answers
13k
views
Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?
At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields.
I have a web interface that interacts with the sql scheduling system.
I'...
0
votes
0
answers
477
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
0
answers
15
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 ...