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
21 views

Transferring records from one instance to another

I have 2 instances of SQL Server 2016, SQLDB1 and SQLDB2. So when a job fails on SQLDB2 instance I want to have a entry for that job failure with the timestamp on a table called "monitor" on ...
0
votes
1answer
21 views

Missing Target Local Server in Job

SQL Server 2016 Enterprise, CU15 + GDR. Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the ...
-1
votes
1answer
64 views

How to kill agent job if it does not finish after x min

I want to create an agent job and collect some data on my server, e.g. every 10 minutes. But I would like to kill the job if it does not finish within a specified time period, e.g. after 5 min. I have ...
0
votes
0answers
23 views

Aborting long running query under AG configuration [duplicate]

Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins. As we have moved to Availability groups ...
0
votes
0answers
14 views

SQL Server Agent on Linux Failover Cluster

We have set up FCI on Linux-SQL server, Everything is working except SQL server agent. Agent is Enable and running but Job are not executing. And Agent also getting stopped very often. Below are some ...
1
vote
1answer
59 views

SSMS Not showing Status and Scheduled Jobs Not Running

Recently in SSMS, I have started getting a blue question mark next to the database and SQL Server Agent, when previously all was showing running. At the same time my scheduled jobs have stopped ...
0
votes
3answers
70 views

SQL Server Agent & sp_send_dbmail failing on permissions

I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the ...
-1
votes
2answers
49 views

Rebuild Indexes [duplicate]

We have a database named WSS_Us_App in our Production SQL Server 2014. In this database all of the indexes need to be rebuilt as most have a fragmentation level greater than 50%. I plan to rebuild ...
2
votes
1answer
121 views

Can't take backup to the shared folder

Need your help. My sql job is not able to take backup to the shared folder although the domain account, under which the sql agent service account is running, has full permission on that shared folder. ...
1
vote
2answers
48 views

SQL SERVER Detailed Backup Report

I need to create a script that gives the report for the following information: Backup Job Name Databases that have been backed up in the last 24 hours Size of each DB backup Start time of backup End ...
0
votes
2answers
56 views

How to get job start time inside of a TSQL agent job step?

Is there a good way to query the job start time inside of a given sql agent job step (TSQL)? This will help with step 3 in the following scenario. Job Foo (Run Daily @ 11:30pm) Step1 - StartTime = ...
1
vote
1answer
41 views

Query agent jobs for a given notification operator name

Is there a good way to query sql agent jobs for success / failure notification to a given operator name? One of the operators is no longer with the company and need to fix up all agent jobs sending ...
0
votes
1answer
49 views

Global Temp Table Error when Stored Proc run by SQL Job on a Schedule

I have a puzzling situation I am trying to confirm/solve. A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This ...
0
votes
1answer
30 views

Report server scheduled job location

I inherited a SQL Server environment. In (database engine) SQL Server Agent are two jobs defined that apparently belong to the report server. First and only step in the first job: exec [ReportServer]....
0
votes
1answer
61 views

Only a sysadmin can specify '@output_file_name'

I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error ...
1
vote
1answer
45 views

Which server actually runs the SSIS package when SQL Agent and SSISDB are on 2 different servers

We have a SQL Server where we deployed the SSIS packages to its Integration Service Catalog (SSIS DB). And we have another SQL Server where we scheduled SQL Agent to run those SSIS packages. My ...
0
votes
1answer
115 views

sp_update_schedule says @schedule_id does not exist

I wrote a script that reads job schedules from msdb.dbo.sysschedules and runs sp_update_schedule to change the active_start_time for a given subset of jobs. In each case, sp_update_schedule fails with ...
1
vote
2answers
29 views

Halting a multi-step Agent job without resorting to “Quit the job reporting success”?

I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures. How can I tell Agent to successfully exit the whole job?...
0
votes
1answer
28 views

SQL Server 2016 -number of SQL agent replication jobs(transaction log repl)

So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent ...
1
vote
1answer
83 views

SQL Server Agent Alerts and Severity Errors

I've setup alerts in SQL Agent for errors of severity level 15-16 for testing, and found out that these are not always triggered To be more exact, these seem to trigger only when given error number ...
1
vote
2answers
30 views

Schedule Job to Start at Different Steps

I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the ...
0
votes
1answer
581 views

How to run Sql Server stored procedure in Async mode?

Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement. Service Broker can be ...
-1
votes
1answer
54 views

How does sql server agent access the azure storage blob [duplicate]

Good morning all , I must configure an automated backup to an azure blob storage in the backup script I have correctly configured the credential now what right is needed for the sql server agent ...
1
vote
1answer
93 views

How to fix SQL Agent Job using Windows Security after password change

I have some sql agent jobs that are using Windows NT Integrated Security to run (as specified in the Maintenance Plan's Manage Connections window)...eg. Translog backups. While logged in as this ...
0
votes
1answer
27 views

Where to find the whole log of a maintenance plan package job that failed with error?

I have a maintenance plan that runs on SQL Server agent as a the job and finishes with error. However the error is not visible in job history in SSMS, the text of Message is seemingly truncated. How ...
-1
votes
1answer
36 views

Complex SQL Agent Job

I would like to tun the Stored Procedures on the following dates. 1st sept 2020 1st Nov 2020 1st Dec 2020 1st Feb 2021 1st March 2021 1st May 2021 1st june 2021 1st august ...
0
votes
2answers
266 views

Grant an AD Group permission to edit all MSSQL Agent jobs

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication. Our AD accounts are added to an AD group, which is used to grant all ...
1
vote
1answer
67 views

User on SQLAgentOperatorRole unable to edit jobs

My Active Directory account is on an AD group that is on msdb's SQLAgentOperatorRole. I'm able to list existing MSSQL Agent jobs, but I can only view their steps and schedules, I'm unable to edit them ...
1
vote
1answer
119 views

SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change

After changing the Collation of SQL Server to Latin1_General_CI_AI from QL_Latin1_General_CP1_CI_AS I'm trying to add the server back to MSX as target in CMS but Enlisting is failing. One thing I ...
0
votes
2answers
41 views

Is it possible to prevent SQL Agent Job from running if related job or previous instance has not finished?

I have three SQL Agent jobs, one for each of three registries being managed. These go off on a staggered schedule every two hours (i.e. Reg 1 at 9am, 3pm; Reg 2 at 11am, 5pm; Reg 3 at 1pm, 7pm). In ...
0
votes
1answer
81 views

SQL Server Agent unable to view network drives

I am unable to get agent jobs to output to a network path. I have pushed the IT guy to set up a domain authenticated user that logs in when the agent starts. That login does have access to the domain ...
0
votes
0answers
36 views

SQL Agent time travel

Can anyone explain why SQL Agent didn't run any jobs between 04:30 and 05:24... The previous execution at 04:30am completed in 8 seconds so it wasn't a long running job. The SQL Server was still up ...
0
votes
1answer
29 views

How to Capture Job Exit Code?

I'm essentially trying to find a better way to determine if my job has completed successfully before moving on to another call of the same job. I have multiple jobs which run through a parsing process ...
0
votes
2answers
79 views

Running queries/jobs via SMSS - SQL service and SQL agent service accounts (SQL Server)

If a command, for example the below command, is executed from SQL Server Management Studio while I am logged in the session with a windows authentication login, I understood that the access to the ...
0
votes
1answer
68 views

Chaining remote SQL Server Agent Jobs with success/failure feedback

I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers. ServerA has a linked server, pointing to ServerB\InstanceB. Both instances have a job, and JobA triggers the ...
0
votes
0answers
115 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
49 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
39 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
29 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
34 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
126 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
165 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
238 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
564 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 ...
1
vote
1answer
376 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
37 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
24 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
30 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
2k 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
115 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 ...

1
2 3 4 5
10