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.
554
questions
0
votes
0
answers
9
views
Running an Agent Job using Azure Shared Access Key Credential
I'd like to have a SQL agent job that copies a file up to Azure Storage using a Shared Access Key Credential.
The credential is set up and works for backup and restore process, but I'd like to use it ...
- 13
0
votes
1
answer
74
views
"Login failed for user 'AD\MyUser'." ... 0x80004005 "Cannot open database "MyDatabase" requested by the login. The login failed."
The full error message:
MyPackageName:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
an OLE DB record is available. Source: "Microsoft SQL Server ...
- 316
0
votes
1
answer
27
views
How do I know which user runs a package in the Integration Services Catalog (SSISDB) from the SQL Server Agent?
I need to find out the user that runs the SSIS packages in the SSISDB but cannot find it. There are so many users and system users attached to the other databases that I cannot learn from that pattern ...
- 316
0
votes
1
answer
80
views
SQL Server Agent log Message [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
We are using Sql Server 2022 on Windows Server 2019.
SQL Server Agent service is runing but some scheduled jobs doesnt start at the time.
When I restart SQL Server Agent service, SQL Server Agent log ...
- 168
0
votes
1
answer
22
views
SSIS package not copying files when run from SQL Server Agent
I have a simple SSIS package that copies files from one directory to another and then deletes the source file. It works fine when run from Visual Studio. Although when I add the package to the SSIS ...
1
vote
0
answers
51
views
sp_send_dbmail fails when query is included
I am running SQL Server 2022 Enterprise inside a company domain. I have a SQL Agent job that runs a stored procedure. The job runs under a domain account say 'domain\myuser' that is a member of '...
- 167
0
votes
1
answer
72
views
Sql Agent jobs, execution contexts, run as issues
We're on Sql Server 2019.
We have a whole bunch of Sql Agent jobs and pretty much by default they are owned by sa.
The company security team issued an edict that all sa accounts are to be disabled for ...
- 235
0
votes
0
answers
38
views
Where are SQL Server Agent driver dependencies documented?
Where does Microsoft document its Driver dependencies for SQL Server Agent in SQL Server 2019? None of the following documents declare a driver version dependency:
(a) SQL Server 2019: Hardware and ...
- 579
0
votes
1
answer
63
views
How to Increase The maximum number of allowed pending SQLServerAgent notifications
I have a large CI pipeline, it runs a series of deployments against a large set of databases(75 to be specific) on a (mostly) single SQL Server(2019). As part of this CI pipeline, and post deployment ...
- 215
0
votes
1
answer
59
views
SQL Job Roles: How to grant a group permission to access SQL Jobs with unique mapping?
Issue
Developers on the same team are members of a Windows_Group.
These developers create jobs which they each own. Only the owner can edit the job so they cannot work together on same job. Because of ...
- 13
-1
votes
1
answer
152
views
Can AWS services replace SQL Server Agent Jobs?
I've decided that I no longer want to use SQL Server Agent Jobs. They're tightly coupled to SQL Server, cause interesting security/permissions problems, and can be complicated to sequence. I instead ...
- 629
0
votes
0
answers
26
views
Run SQL Server Agent T-SQL Job step under another Windows Account
This may look like a duplicate of this post, but the case differs. Here is the complete situation:
I have access to 172.31.50.98 using my Windows Domain Account
I have added 172.31.50.98 to my ...
- 1
3
votes
1
answer
476
views
What exact error messages do UMPPC*.DLL and SCRIPTCONTROL*.DLL cause SQL Server Agent when it fires SSIDB packages?
Microsoft's maintains a list of bad DLLS. This is one item on that list (emphasis mine)
UMPPC*.DLL and SCRIPTCONTROL*.DLL
These DLL files are loaded in to the address space of SQL Server related ...
- 629
0
votes
1
answer
70
views
SQL Server Agent Alert to Detect deadlocks
Some SQL Server Agent jobs failed recently with deadlock errors. I wanted to keep an eye on the issue and created a monitoring alert in SSMS. However, the event never fires.. which seems wrong.
Alert ...
- 317
2
votes
1
answer
51
views
How to retrieve SQL Agent failsafe operator email?
I want to find the email of the failsafe SQL Server Agent operator.
Usually I use this query :
DECLARE @TARGET nvarchar(255);
IF @OPERATOR_CONTACT IS NULL
exec master.dbo.xp_instance_regread
...
- 505
0
votes
0
answers
114
views
Incorrect Next Run on Monthly Agent Job
Users have reported that a job on a recurring schedule has not been firing. This is also evident in the execution history.
After some experimentation, it looks like it might be a bug in the SQL Agent.
...
0
votes
0
answers
117
views
How to find a deleted SSRS subscription that still runs
In troubleshooting an 'SSRS won't email subscriptions' problem I created a test subscription. I then found the SQL Agent job and changed the owner (I read the disclaimer in the job description, ...
- 101
-1
votes
1
answer
89
views
What context does the agent service account use (logon account or the NT SERVICE\SQLSERVERAGENT)?
My SQL server agent service account is running under the following domain user account: mydomain\svc_svr1_sql_agent
I have a ssis package that must be able to read/write files in a folder.
The ssis ...
- 3,455
0
votes
1
answer
46
views
SQL Server Agent Job sometimes doesn't execute as User
I have a SQL Server Agent job that is set to run as a domain user. It runs a node command and had been working with no issues for months.
Now every few days it fails to run with the following error
'...
- 123
1
vote
1
answer
85
views
How can I make a self-updating Agent Job refresh itself?
I have a SQL Server Agent Job which is designed to reconfigure itself in response to conditions that are in place when the job starts.
So, in essence, the first step updates the second step. That ...
- 70.3k
0
votes
0
answers
51
views
SQL Agent job step update permissions
I have a non-sysadmin user whose ID (Windows login) is set as owner on a set of SQL Agent jobs. The user also has the SQLAgentReader role in MSDB. The user can see the Agent node in SSMS, can view ...
1
vote
1
answer
70
views
Job history cleanup query being blocked
I'm running the below query every hour to cleanup job history in the sysjobhistory table for all jobs that execute every 10 seconds. The query aims to delete 100k rows of job history that is older ...
- 69
1
vote
2
answers
468
views
How can I save the job result to a file?
I created a job in SQL Server. This job runs the following statement at regular intervals:
Select NationalIDNumber from HumanResources.Employee where BusinessEntityID = '1'
I want to save the result ...
- 57
0
votes
0
answers
42
views
SQL Server Agent ignores new job attached to existing schedule -- no errors, no messages, no execution
SQL Server: 15.0.4326.1 (2019 Standard Edition, current through CU22 security update )
I have an existing schedule on a SQL Server named Daily0230. There is one existing job attached to that schedule....
0
votes
0
answers
82
views
What permissions does SQL Server Agent Account needs to run the job?
I am wondering what kind of permissions [NT SERVICE\SQLSERVERAGENT] accounts needs to run the job?
Below are the steps that have been impleemented yet the job doesn't run with correct results expected....
- 577
-1
votes
2
answers
987
views
SQL agent doesn't start after ODBC upgrade
For security reasons, I would like to upgrade the ODBC driver from version 17 to version 18, but when I do this, the SQL agent does not start. How to configure it to start?
The log says: Windows could ...
- 1
1
vote
0
answers
59
views
Azure Elastic Job - a stored procedure does not run on the target database
The target group NPBlue contains 3 databases
NPBlue 882E2367-93C0-46CC-A4ED-90AE5F2860EB Include SqlDatabase 69B479D8-34FA-48A6-9A3D-6B302864B9AD NULL NULL NULL x-database-weu.database....
- 11
-3
votes
1
answer
517
views
System dbs have different collation
i am new on this forum.i have installed an instance of sql srv 2016 sp2 called DNF containing 2 dbs and also the system dbs.after providing the instance to the dev team the realise that they need to ...
- 1
0
votes
1
answer
83
views
Developer with SQLAgentUserRole only has View access
I have a developer who needs to be able to create jobs in SQL Server Agent. They are part of the security group SQLDA and I granted that group SQLAgentUserRole in msdb. They still have view only ...
- 173
0
votes
0
answers
64
views
Cannot perform this operation while SqlServerAgent is starting problem
I use SQL Server 2017, this mornnig when i check jobs i got this error;
I searched a lot but couldn't find a solution.
Does anyone know the solution to the problem?
thanks.
- 11
0
votes
0
answers
48
views
Deleted SQL Agent Job Sending Failed Notifications
I have deleted the job and I don't see it in the object explorer nor in sysjobs, and yet it is sending out notifications when it 'fails'. I've done the following solution:
Restart MSSQLSERVER & ...
-1
votes
1
answer
106
views
Is there a better way to do Business Logic job scheduling than SQL Server Agent [closed]
I was listening to a recent Brent Ozar Office hours and someone asked about using SQL Server Agent or Windows Task scheduler to schedule business logic jobs. Ozar mentioned that neither of those is ...
- 14
0
votes
2
answers
851
views
SQL Server Agent not starting after in-place upgrade of SQL Server from 2012 to 2019
I have done an in-place upgrade of SQL Server from SQL Server 2012 Standard Edition (64-bit) SP4 to
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 ...
0
votes
1
answer
982
views
Access Denied error when running job in SQL Server Agent
Problem:
While trying to execute an exe (kept in network path) through Sql Job (type: Operatingsystem(CmdExec)) below error occurred:
Executed as user: NT Service\SQLAgent$SQL2017STD. The process ...
- 105
0
votes
1
answer
343
views
Run SSIS Package but the package path will be decided runtime
I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps.
My idea ...
- 119
2
votes
2
answers
877
views
Create a Job Schedule that runs on the 1st of month only if it falls on a Sunday
This job already has one schedule that runs on the first of the month.
However, we need to run the job on a different schedule if the first of the month falls on a Sunday.
It appears to me that the ...
- 41
0
votes
1
answer
185
views
How do SQL Server allocate RAM for Job steps?
I am working with SQL Server 2016 and SQL Server 2019 Standard edition. We have this job that's been there for a while. It used to be small but now it runs dozens of SELECT/DELETE statements. (There ...
- 2,029
1
vote
1
answer
2k
views
How can I add time and date to a query output file name in sql server agent job
I have a query set up in SQL Server agent to return an output result file. The problem I have is how to set a date and time stamp so that the file name always includes the date and time.
On the SQL ...
- 13
1
vote
3
answers
539
views
Centralized reporting of SQL agent job status
We have a requirement to design some sort of reporting the status of SQL agent job status for all the sql servers in the org.
I am able to do it via querying dmvs in msdb directly for that instance ...
- 794
0
votes
1
answer
635
views
Trigger agent jobs on failover
I currently have a 2 node AG with automatic failover enabled. Some of the databases in the AG are also using change data capture, as such we have CDC_capture and CDC_cleanup jobs. The jobs are ...
- 388
0
votes
1
answer
321
views
SQL Agent Job fails after upgrade to SQL 2017
I have upgraded a database to SQL 2017. A SQL Agent Job that previously ran now fails with this error:
Unable to start execution of step 1
(reason: JobOwner DOMAIN\USERNAME doesn't have permissions ...
- 111
1
vote
1
answer
2k
views
Sql server agent job question - maximum run time
How long can a SQL server agent job can run in general? are there any maximum run time limit settings option while creating a job ?
- 31
4
votes
2
answers
2k
views
Why does this query fail when I run it from a SQL Server job?
I know how to solve the issue, but I don't understand the technicality of why the below query would work when I run it from SSMS, but the same query fails when I add it as a job in the SQL Server ...
- 100
0
votes
2
answers
3k
views
SQL Server Agent not running SSIS package
I have followed many articles to create an SSIS pipeline package, deploy it and schedule it.
I'm trying to transfer data from two remote servers, an OLEDB driver that connects to a SQL Server Database ...
0
votes
0
answers
476
views
SSIS and SQL Agent job impersonation
I currently have a new SSIS machine. SSIS is used to automate maintenance task such as backups and restores of other servers on the network. We have over 600 servers in our line of business and each ...
- 134
0
votes
1
answer
43
views
Stop Changes to Maintenance Plan from Renaming Linked SQL Agent Job
Is there a way to stop a SQL job (which calls a maintenance plan) from being renamed every time i open and save that maintenance plan?
Each time i do that, a prefix of "_SubPlan" gets added ...
- 11
0
votes
4
answers
2k
views
SQL Agent Job - Change all job steps to go to a specific step when the job fails
I have a SQL Agent Job that I need to change, so that any time any step fails the 'ON FAILURE' action goes to a specific step.
Does anyone have a script or can you show me how to write this so that I ...
0
votes
1
answer
2k
views
SQL Agent Job error
It may be really simple question, but at the moment I have a kind of mental loop and need your help to break it.
SQL server 2016 is installed using a domain account. I didn't create an explicit login, ...
- 173
0
votes
0
answers
174
views
Why can I not manually run more than one snapshot agent at a time
My organization has a SQL Server 2016 instance with multiple databases, and multiple publications/subscriptions for each database, in a transactional replication set-up.
Occasionally, we need to ...
- 1
0
votes
1
answer
405
views
Scheduling Next Run date for weekly agent jobs
On SQL Server I used maintenance plan wizard to create three jobs:
Job
Schedule
Job 1
Every 3 week on Sunday
Job 2
Every 3 week on Sunday
Job 3
Every 3 week on Sunday
I want the three jobs to run ...
- 461