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
0 answers
10 views

Save Query Results into Excel and Automatically run it

I have a very simple query Select * from Employees I want to run this query automatically on a daily basis and save the results in excel using SQL Server. Is there any way I can achieve this purpose? ...
1 vote
1 answer
45 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
0 votes
2 answers
81 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 ...
0 votes
0 answers
14 views

Why would filter settings in SQL Server Agent - Jobs only show Name as a filter option?

I experienced this recently in SSMS when trying to apply a filter in SQL Server Agent --> Jobs --> Filter --> Filter Settings. Currently, the only filter settings option I have is Name. I ...
0 votes
0 answers
21 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
59 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 ...
0 votes
0 answers
60 views

Can't start SQL Server Agent in SQL Server 2019 in Docker Linux

I installed SQL Server 2019 from Docker Hub in Docker Desktop on my Intel Mac using docker pull mcr.microsoft.com/mssql/server:2019-latest. In SQL Server Management Studio (running on Windows 10), SQL ...
0 votes
0 answers
69 views

xp_sqlagent_enum_jobs - Unable to enumerate Jobs due to failure to obtain serialization object

I a procedure whose job is to ensure that certain SQL agent jobs do not overlap with other jobs, the logic is as follows(Simplified): CREATE TABLE #Job ( job_id UNIQUEIDENTIFIER NOT NULL ,...
1 vote
1 answer
321 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
520 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 ...
  • 88
-1 votes
2 answers
542 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
173 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 ...
0 votes
1 answer
27 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 ...
0 votes
4 answers
343 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
283 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, ...
0 votes
0 answers
48 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
100 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 ...
  • 421
4 votes
2 answers
288 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
43 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 ...
0 votes
0 answers
26 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,069
1 vote
2 answers
355 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 ...
  • 655
0 votes
1 answer
275 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 ...
0 votes
1 answer
413 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
161 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 ...
1 vote
0 answers
187 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
78 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
87 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
181 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 ...
0 votes
2 answers
597 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 ...
1 vote
1 answer
69 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 ...
  • 2,874
2 votes
1 answer
307 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
58 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 ...
  • 195
0 votes
0 answers
444 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
220 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, ...
0 votes
1 answer
139 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 ...
2 votes
1 answer
206 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 ...
1 vote
1 answer
1k 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 ...
2 votes
2 answers
59 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
64 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,741
0 votes
1 answer
54 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 ...
  • 9,508
-1 votes
1 answer
359 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 ...
  • 655
0 votes
0 answers
26 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 ...
  • 2,137
1 vote
1 answer
1k 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
3 answers
804 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 ...
  • 123
-1 votes
2 answers
56 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
1 answer
272 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,193
1 vote
2 answers
60 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
2 answers
202 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 = ...
2 votes
1 answer
373 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
1 answer
328 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 ...

1
2 3 4 5
11