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
4
votes
1answer
8k views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS

I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from visual studio or from SQL Server Management Studio, it ran ...
0
votes
1answer
121 views

SQL Server: Description: The version of Destination - ***.csv is not compatible with this version of the DataFlow

I have downloaded SQL Server 2016 evaluation onto a Windows 10 laptop. I have imported some data into a new DB and new table. So far so good. I have exported the data to a CSV file and save this as ...
1
vote
2answers
138 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
0answers
36 views

SQL Server Agent log is showing error [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite)

While checking SQL Server Agent Log i found lot of error like follows [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite) [298] SQLServer Error: 10004, ...
2
votes
6answers
2k views

SQL Server 2012 Job History Missing

I have a SQL Server 2012 database with a number of Agent jobs. The jobs run without a problem but the job history is always empty. Why? I also note that running a job manually (right click and "...
-1
votes
1answer
298 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 ...
1
vote
0answers
43 views

Why is failed job not logged in SQL Server Agent Error Log?

I have a failed job which is logged in SQL Server Logs but not logged in SQL Server Agent Error Logs. The job is executed as user: NT SERVICE\SQLSERVERAGENT. Limit size of job history and Remove agent ...
0
votes
0answers
101 views

SQL Server Agent runs a job on one server, not on the other

I have two SQL servers, each running on their own Windows server. The versions of both Windows and SQL Server are the same, they are on the same domain, they're virtually identical. On these servers,...
4
votes
1answer
198 views

Why the job's next_run_time is wrong?

I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017. ...
4
votes
1answer
282 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, ...
1
vote
1answer
28 views

SQL Server Agent - Report Failure but continue When intermediate step fails

I have a SQL Server Agent job that has three steps with the following control flow: Step 1 - on success - Go to next Step. on fail - job fails Step 2 - on success - Go to next Step. on fail - Go to ...
2
votes
2answers
12k 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 ...
0
votes
0answers
20 views

Takes several hours to commit data on my data warehouse what seems for only one table

I have a data warehouse which runs several jobs during the night in order to update data. Background and job execution(s) setup One of these jobs updates the FactSale table consisting of the sales. ...
1
vote
1answer
48 views

SQL Agent job for SSIS execution fails with “An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed.”

A SQL Agent job with a SQL Server Integration Services Package step fails with the following message: Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility ...
4
votes
3answers
1k views

Is there a way to back up agent jobs?

I can't find documentation about whether agent jobs in SQL Server can be backed up. If not, how can I back them up in order to restore during the future recovery in case of an instance failure?
15
votes
3answers
46k views

What is the Query to display the failed sql jobs

What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query
1
vote
0answers
26 views

SQL Server allow viewing of jobs by job category

I know how to assign or create "categories" for jobs. But is there a way to actually view jobs by category by default. I do not want to see all the jobs just listed within SQL Server Agent. I'd ...
0
votes
1answer
25 views

SQL Job PowerShell task fails same command succeeds as user

Simple requirement: delete files on remote path older than 20 days. PS C:\Users\Administrator> Get-ChildItem ` -Path \\FileServer\SQLBackups\SQLServer\ ` -File -Recurse -Force | Where-...
0
votes
2answers
768 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
76 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 ...
1
vote
1answer
35 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 ...
2
votes
3answers
41k 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
115 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
72 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
28 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
178 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
75 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
25 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
16 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
112 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
204 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
2k 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
69 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 ...
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
446 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
10k 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
64 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 (...
0
votes
1answer
118 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
32 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
68 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
68 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
343 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
426 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 ...