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.
532
questions
0
votes
0
answers
44
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
1
answer
84
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
25
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
363
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
35
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 ...
- 161
0
votes
0
answers
43
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
19
views
Parameterized Connection Manager in SSISDB Job not using the set ODBC password
Regardless of the solution I find online I have been unable to get my SSMS Scheduled Job to successfully complete my ODBC-connected SSIS ETL Packages.
I have tried deploying the SSIS project/packages ...
0
votes
0
answers
40
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
64
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
0
answers
60
views
How to alert when a new SQL Agent Job is created in SQL Server 2019?
How do you alert when a new SQL Agent Job is created in SQL Server 2019? Is there some kind of trigger that would work here?
- 1,087
0
votes
2
answers
561
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
611
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 ...
0
votes
1
answer
229
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 ...
- 19
2
votes
2
answers
578
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
114
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 ...
- 1,999
1
vote
1
answer
1k
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
305
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 ...
- 774
0
votes
1
answer
351
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
281
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
1k
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 ...
- 90
0
votes
2
answers
2k
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
386
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 ...
- 135
0
votes
1
answer
39
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
1k
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
1k
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, ...
- 171
0
votes
0
answers
144
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
359
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 ...
- 441
4
votes
2
answers
806
views
Suppressing SQL Server Management Studio's 'Messages' from output of SQLCMD via SQL Server Agent
I've created a script that outputs data in an XML format, and was using SQL Server Agent (Job Step of Type 'Operating System (cmdexec)') to run a job monthly/quarterly to output the data to a specific ...
0
votes
0
answers
109
views
emails sent by a phantom job: find a sender job
Every night at the same time I receive some mails like that:
DESCRIPTION: Error: 50000 Severity: 16 State: 1 [ERROR]:DWHDumpProcess_XXX job is either failed or not in good state. Please check. Return ...
- 171
0
votes
0
answers
91
views
enable job results in a sp_send_dbmail error
On one of our production servers (SQL SERVER 2017 Enterprise) I get the following error whenever I right click and enable a Job in the SQL Server Agent:
Alter failed for Job 'My Job'. (Microsoft....
- 2,109
1
vote
2
answers
1k
views
Find Agent job name from PROGRAM_NAME in sys.dm_exec_sessions
I would like to find the name of an Agent job, but e.g.
Select login_name,
program_name,
host_name,
nt_domain,
nt_user_name
From sys.dm_exec_sessions
Does not show the ...
- 795
0
votes
1
answer
635
views
DBCC CHECKDB job failed on SQL Server 2014
We have a DBCC CHECKDB job that runs weekly on Saturdays. This job was created thorugh Maintenance Plan wizard. Typical duration for this job is 1.5 hours. This past weekend the job failed with the ...
- 83
0
votes
1
answer
1k
views
SQL Server create cron job without SQL Server Agent
I'm creating a cron job on SQL Server. As per my knowledge, the only way possible to create a job is through SQL Server Agent. However, the services(listed on services.msc) of VM assigned to me do not ...
- 13
0
votes
0
answers
475
views
Sql Job Failing with Timeout Error
Recently I have noticed that a job is failing on our Production 2014 SQL Server with this error text:
Timeout error [258]. [SQLSTATE 42000] (Error 258) OLE DB provider "SQLNCLI11" for ...
- 83
1
vote
0
answers
496
views
SSIS and SQL Agent - running jobs using proxy vs using MSA
We have multiple servers, each with its own SQL Server instance.
We have external companies that develop applications for us. This companies deploy SSIS packages and run them via SQL Agent.
I'm ...
- 141
0
votes
2
answers
122
views
Does changing database growth rate force a new execution plan?
For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on ...
0
votes
0
answers
125
views
Stored Procedure fails on tempdb-diskspace when executed by SQL Agent
I developed a bunch of ETL procedures which are executed by an overlaying SQL Script and write Data to separate DB. The Script uses a single Transaction, ensuring we only get complete data.
This ...
1
vote
0
answers
549
views
Sql Server Agent Job - Unable to start execution of step 1 (reason: Error authenticating proxy "user", system error: The user's account has expired.)
My predecessor created several "Intergration Services Catalogs" dtsx packages which are scheduled / run via SQL Server Agent -> Jobs using an SSISFileProxy which is using ...
- 11
1
vote
2
answers
2k
views
SQL Server Agent Not Executing Job
We tried to schedule job in Sql server to take database backup.
But it has been failed to execute.
While checking logs we could see 2 different errors.
SQL Agent error logs
Error,[382] Logon to ...
- 11
1
vote
1
answer
845
views
Where can I find sql server agent service logs?
I am using SQL server 2014.
I notice that - occasionally, the sql server agent service gets automatically stopped.
Where can I find logs to check the timing of when the service stop has happened and ...
- 3,442
2
votes
1
answer
1k
views
Job to stop other job at specific time
We have a job which imports data daily from a remote SQL Server every night, I'll call it Import-Job. Most of the time this job runs through in about 15 minutes and everything is fine but due to a ...
- 131
0
votes
0
answers
107
views
Sql Error Message ID 3013 & 4305 for Standby / Read-Only database that we use for import data heavily
Sorry. I am not a DBA, but a SQL Developer, so I am trying to get some general support/feedback on my case (and when I Googled the issue, remedy seems so scattered so I decided to post here).
I got ...
- 213
0
votes
0
answers
980
views
Permissions to run SQL Server Agent Job
I am getting the following error message:
Message Executed as user: fin\jdoe. The server principal
"fin\jdoe" is not able to access the database "abc" under the
current security ...
0
votes
1
answer
298
views
Run T-SQL Script Using Powershell via SQL Agent Job
Tried to think of the best subject title; however having a problem getting this done. I'll give my preferred way, but open to any best method to get this done.
I have a tsql script that is done, ...
- 21
0
votes
1
answer
297
views
SQL job sending email even if not configured to send notifications
I am using this version of SQL Server.
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
- 1,999
2
votes
1
answer
388
views
SQL Agent stopped recording history (SQL Server 2017)
I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server.
On 2021-06-05 something happened on this server that altered ...
- 193
1
vote
1
answer
2k
views
Security best practices for SSIS package execution via SQL Agent job
We're trying to rein in the number of sysadmins on our servers, and one of the issues we're running into pertains to SQL Agent security -- especially with regard to SSIS jobs and our ETL team. I ...
- 75
2
votes
2
answers
135
views
In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal?
In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available....
- 21
2
votes
1
answer
162
views
Jobs on the Mirror are running against the Primary
I have a mirrored set of jobs between the my current primary and mirrored failover SQL Server setup. In the past, the jobs on the mirrored server, when they are enabled, fail as they are not able to ...
- 1,771