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
2answers
701 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
0answers
44 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 ...
2
votes
1answer
64 views

SQL Server Agent Job is Delayed?

I have an SQL Server Agent job set up to run every minute on the minute. Usually it runs exactly at :00 on the clock but occasionally it waits several seconds. See below, one of the jobs was ...
0
votes
2answers
107 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
1answer
32 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 ...
0
votes
0answers
17 views

What are drawbacks of running CmdExec job step?

Is there any risk of running CmdExe job step in SQL Server? Which one is more secure, running powershell script via task manager (windows) or that of via SQL Server agent (CmdExec)?
2
votes
3answers
36k 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 ...
3
votes
1answer
1k views

Intermittent errors sending email from SQL Server Database Mail

I've been seeing some SMTP errors in my database mail logs. So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email ...
3
votes
1answer
44 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 ...
0
votes
1answer
54 views

SQL agent job for long running queries

I am trying to setup an alert where the application team can get the notification on their event viewer for any queries which are running longer than 2 minutes. This is what I have so far. For some ...
2
votes
1answer
27 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
2answers
145 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-...
0
votes
1answer
51 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
1answer
24 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
1answer
32 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
0answers
10 views

SSIS Jobs are getting hung

Job calling SSIS Packages are getting hung, When I restart the job, job shows running in but I can see below error in SQL agent Error log. Restarting SQL agent solves the issue. This happened ...
5
votes
2answers
2k views

Issues running SSIS package as SQL Server Agent job

I've got some ridiculously stubborn difficulties when trying to run SSIS packages SQL Server Agent jobs on two SQL Servers, one in a domain and one without. I've read hundreds of posts on dozens of ...
0
votes
1answer
72 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
1answer
184 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
3answers
1k 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
0answers
56 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 ...
0
votes
1answer
268 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 ...
13
votes
4answers
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
2answers
378 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 ...
5
votes
5answers
9k 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
0answers
43 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
0answers
12 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 ...
0
votes
1answer
63 views

TSQL Agent jobs owned by users

I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job. I get tons of security findings about agent jobs owned by users. Most of these hits are system-type accounts (...
5
votes
1answer
263 views

Handling 'Variable not found' in a tokenised job step

I have an Agent job which is usually triggered by a WMI event. The job has a token within it, which automatically takes a value from the WMI event and substitutes it into the step's Powershell code, ...
0
votes
1answer
58 views

T-SQL for starting Log Reader Agent

I am facing a problem regarding SQL Log Reader Agent. Whenever, I start SQL Server Agent, Log Reader Agent does not start. I have to start manually. Do I have start Log Reader Agent manually every ...
0
votes
0answers
31 views

Automatic Query from root DB to new DB

I have access to an enterprise level DB used throughout the organization with tight constraints on who can access things. It contains almost all our data. I am a user of that DB with only read rights. ...
0
votes
1answer
383 views

How to influence SQL Server Agent Caching

I noticed that Sql agent is caching certain informations when running jobs. Since I have no permissions to start or stop the Sql agent, I'm looking for a way to influence this caching behavior (e.g. ...
0
votes
1answer
63 views

Red gate SQL Compare error while running using SQL Agent Job

I created a SQL agent job to perform a SQL Compare using the below command line powershell.exe -File D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.ps1 This below given is the command line code in DEVvsPRJ....
1
vote
1answer
50 views

Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts ...
0
votes
0answers
18 views

Start Sql Agent Service with non-administrator priviliges

I'm trying to start SQL Server Agent service with domain account that is not part of administrator group. I found this https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms143504(v=...
2
votes
1answer
336 views

SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell

I have a SQL agent job set to execute a SQL query against a single database and then save each row into a different file with powershell before zipping the files up and emailing me. In one of my SQL ...
4
votes
2answers
304 views

Start job from another SQL server instance

I have a job on an SQL 2008 server (Server A) (I know this is not ideal... That issue is being addressed). The agent runs under NT AUTHORITY\NETWORK SERVICE. I want to add a step that runs a job that ...
5
votes
2answers
243 views

What happens if agent job owner account is deactivated in Active directory?

We have a number of jobs owned by a DBA. The DBA is now leaving the company so their account will be deactivated in Active Directory. What affect will this have on the agent jobs?
4
votes
1answer
811 views

Server Activity data collector job fails due to performance counters

tl;dr: what Windows permission / configuration is needed for the "Server Activity" data collection job to run successfully? I'm running SQL Server 2016 SP1 CU6 (13.0.4457.0) on Windows Server 2012 R2 ...
2
votes
0answers
118 views

22046 “Impersonation error” running SQL Server Agent job

(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. ...
0
votes
2answers
295 views

Rebuild index error: “The job was stopped prior to completion by User”

I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 ...
0
votes
1answer
16k views

The job failed. The Job was invoked by Schedule 1012

I am new on maintaining database backups, i have problem on my scheduled transaction logs, it was working before until i encountered the error below. Message: The job failed. The Job was invoked by ...
1
vote
0answers
96 views

Getting non owner to execute a job

I've attempted to implement solution three from the following Q & A: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it Table created and stored procedure created in msdb, ...
20
votes
3answers
50k views

Giving file system access to the SQLSERVERAGENT virtual account

I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in ...
2
votes
3answers
3k views

SQL-Server login-error of SQL-Server Agent Account

I have to manage a configured SQL-Server version 2005 and have setup Database Mail successfully - test mail works. Also I've changed the agent service to a domain user with (currently) domain admin ...
0
votes
0answers
21 views

Can target servers use master server job categories?

I'm using multiserver administration to deploy agent jobs to a group of target servers. I have a multiserver job category setup on the master and multiserver jobs are added to it. All looks good on ...
1
vote
0answers
39 views

AWS RDS SQL Server Native Backups

We have some AWS RDS SQL Server instances that we are setting up. Some of these will be Multi-AZ for HADR. We know that RDS will take system level backups and snapshots. We are wanting to perform ...
2
votes
1answer
11k 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 ...
3
votes
3answers
2k views

SQL Server Maintenance Plan multiple database backups in a single folder

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day. For ex. I need to backup db1, db2, db3 and dump all these 3 ...
6
votes
2answers
3k views

What is the best way to include SQL Server Agent jobs in Source Control?

I found that if I script a SQL Server Agent job as 'create to' that the script starts with use msdb- so I assume that jobs are stored in the msdb database? What is the best way to add SQL Server jobs ...