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.

86 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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, ...
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 ...
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 ...
3
votes
2answers
42 views

Query to check if job already belongs to jobserver in MSSQL

I am working with a legacy migration script that was setup to add a job (With a number of steps) to a MSSQL server. I am trying to build in some semblance of re-runable without it completely blowing ...
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
0answers
566 views

SQL Server Event Alerts Lack Enough Details

I setup SQL Server Alerts to send me emails whenever certain events (by severity) occur. Below is an example of en email I received for a "014 - Insufficient Permission" severity event of Type "SQL ...
2
votes
0answers
119 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. ...
2
votes
0answers
140 views

AWS - SQL Server Agent “white dot” problem

I'm trying to setup a SQL Server 2016 installation in AWS EC2 and can't seem to get the security group settings right. Please note the "white dot" in the screen cap below - I am unable to administer ...
2
votes
0answers
55 views

Controlling or deugging validation behavior of SSIS packages

After investigating a production issue, I have found a number of references to SSIS packages getting stuck in 'validation' when run with DTEXEC or through as SQL Agent job steps. Specifics of my ...
2
votes
0answers
889 views

SSIS or SQL Agent weird error

We are like SSIS heavy shop and there are more than 30+ packages that runs in that 24 hr cycle. However, lately every few other weeks we are noticing that the packages fail to execute and throws out ...
2
votes
0answers
28 views

Is sp_stoppublication_snapshot considered a undocumented procedure or is my searching just bad?

I am trying to start and stop the replication snapshot via t-sql. I found sp_startpublication_snapshot, and doing a trace while hitting the stop on the snapshot from inside SSMS I see it calls ...
2
votes
0answers
225 views

How can I have SQL Server send Job Notification alerts to an API

I am trying to get the notifications for a job out of my email and into a chat client(HipChat). So far my googling has been less than successful so I will put the question to the community: Is there ...
2
votes
0answers
370 views

SQL Agent parsing actually executes instead of just validating

I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident. This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory. I ...
2
votes
0answers
308 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
2
votes
0answers
638 views

SQL Agent - PSexec remote commands causing SSIS packages to hang

First post here, so please be kind! I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup. Each job is a multiple step ...
2
votes
0answers
561 views

Receiving historical SQL alerts when SQL Server agent is restarted

The issue is faced in SQL server 2014, whenever SQL Server Agent is restarted or if the server reboots, there are historical SQL alerts which are received and emails are being sent to the various DLs. ...
2
votes
0answers
81 views

Is it possible for a user that is not the job owner to edit the job or add new steps?

My development lead is ok to revoke the sysadmin privileges he currently has, if there is a way he can still edit/execute/enable-disable/execute application jobs. So far, I see that with the ...
2
votes
0answers
519 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. domain\...
2
votes
0answers
523 views

send custom email when SQL Server job fails

I have setup a profile and account for sending mail using DatabaseMail. I have a SQL JOB. If the job fails, I receive an Email Till this point, everything works fine. This is what the email looks ...
2
votes
0answers
213 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
1
vote
1answer
51 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 ...
1
vote
0answers
40 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 ...
1
vote
0answers
202 views

DB Restore Agent Job Fails After “..running the upgrade from version 695 to version 696.”

Recently I have started to see an intermittent error when trying to restore database 'X' via an agent job. The job runs a T-SQL script that restores a database from a 2008 R2 (production) instance to ...
1
vote
0answers
19 views

SQL Agent Job: Seeking Process flow advice on managing steps

Fellow DBAs, I need some practical advice on how to solve a job timing issue on different servers. I have a SQL Agent job that currently has steps 1-10. Step #8 has a dependency on a SQL Agent job on ...
1
vote
0answers
199 views

SQL Agent Powershell step not using specified proxy

I'm trying to execute a powershell script, and use a proxy to do. The job runs correctly, but using whoami within the script to log the executing user indicates that the script is not being run by the ...
1
vote
2answers
53 views

Get-SqlAgentJobHistory Invoked By

I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account. I'm running this cmd: Get-SqlAgentJobHistory -ServerInstance $...
1
vote
0answers
131 views

OLA Output File Cleanup - Better Options

Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
1
vote
0answers
246 views

SSIS job runs successfully but doesn't load data

I am running a job from SQL Server Agent and the job runs successfully. However it doesn't load any data (in my case Excel files from a local folder). I created a new Proxy Account to run this job but ...
1
vote
0answers
648 views

SQL Server Agent stopped - Event ID 17052

I've had back to back Saturdays where our SQLServerAgent (Test Instance) has stopped with an Event ID of 17052. The times do differ as the initial week was 21:00 and last Saturday was 19:00. Having ...
1
vote
0answers
102 views

SQL Server Agent Alert does not pick up some of Events from Application Log written by SQL Server Audit

My end goal is to be emailed when new object (schema, table, procedure etc.) is created / altered / dropped at one of my databases on SQL Server I created a server-level audit, that writes to Windows ...
1
vote
0answers
1k views

SQLServerAgent Error: The process cannot access the file because it is being used by another process

I have created the following as an automated job to run at midnight on every database server I manage: This job fails intermittently on some servers, but not others and I get the following error: ...
1
vote
0answers
44 views

Access Secondary with SQL Agent Account - Security Question

I have installed three SQL Server Instances on three different server and created an availability group using all three instances. The sql server engine service runs on all three instances with the ...
1
vote
0answers
616 views

Restarting SSRS using SQL Agent & Powershell

I'd like to create a SQL agent job to restart SSRS each day. In the past i've used a Powershell script executed by a Scheduled task taken from here. This has worked fine, but I would prefer it to ...
1
vote
0answers
309 views

SQL12/14 Maintenance Plans - Local Server Connection Failure

So interesting problem that I ran into with regards to maintenance plans and SQL Server 2012/2014. The test case for this is on a clustered Windows 2012 R2 environment install SQL of your choice ...
1
vote
0answers
1k views

sql the job was stopped prior to completion by shutdown sequence 0

I've transaction replication set up and running fine but today my Log Reader agent started having this warning : sql the job was stopped prior to completion by shutdown sequence 0 . The job was ...
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, ...
1
vote
0answers
84 views

syspolicy_purge_history 3rd step runs forever on a cluster node?

I have a SQL Server cluster. On one of the nodes, the syspolicy_purge_history job runs forever on step 3. I did stop and restart the job, however, again it is running forever at step 3. How can I ...
1
vote
0answers
319 views

SQL Server Agent Jobs starting but is idle

We have a data warehouse with a few databases. Every morning we import data from source systems to our data warehouse. The final step used to be to initiate loading of an Essbase cube by a SQL job ...
1
vote
0answers
223 views

SQL Job output as YYMMDD

I would like to ask how am I able to export my output file as YYMMDD.txt format in SQL Job? Right now i'm using SQL Token with $(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME)).csv My output now will ...
1
vote
0answers
4k views

Where is the default location for the SQL Agent Job step output file? How do I update it?

I recently migrated my system databases to a new location. I then experienced failures of my SQL Agent jobs with the following error: 2015-10-03 01:53:10 - ! [136] Job - reported: Unable to ...
1
vote
0answers
42 views

SQL Server 2005 Database Mail Resends Historic Messages following SP4 Build 5324

I have applied SP4 Build 5324 to a SQL Server 2005 Enterprise active/passive cluster (Build pathway: 4053 to 5000 to 5324). The SQL Server log contains zero errors. The SQL Server Agent log contains ...
1
vote
0answers
244 views

SQL Server agent error on a the mirror database

I configured mirroring on one of my databases. The database is in a SQL Server cluster. The mirroring is working great, but all the jobs that set to run fail. The error is: Date 9/10/2015 8:...
1
vote
0answers
195 views

Good SQL Agent job wrapper… TRY/CATCH, Custom logging. (Review and provide input)

I'm looking for input on a small snippet of code i wrote. We use SQL Agent jobs to prepare big databases from production, before we ship the to test-environments. To get better feel for duration of ...
1
vote
0answers
558 views

How to check particular SQL UserID windows permissions in MS SQL SERVER 2012?

I would like to say as per my network system administrative security of windows Server 2012 R2. I am unable to start SQL Server agent. As per in MS SQL Server 2012 , i have "sysadmin" & "public" ...
1
vote
0answers
218 views

Sql agent job step to Rename a file in SQL Server 2000 fails with error “ file being used by another process”

We have a SQL agent job configured on Sql Server 2000, which has an initial step of renaming a backupfile coming from a another server (SQl Server 2000) Say coming from \A\B\X.dat to X_back.dat. till ...
1
vote
0answers
959 views

SQL Server Agent Job, Access Denied

I have a T-SQL agent job that I've created that adds steps to itself depending on a query of the MSDB database. I am able to run the query outside of the agent, inside a query editor window, but when ...
1
vote
0answers
628 views

What are recommended (working) permissions for a user to view all SQL Agent jobs

A new user (associated with an existing login) is created in msdb and assigned membership: use [msdb]; create user [TheUser] for login [AnExistingLogin]; alter role [SQLAgentUserRole] add member [...
1
vote
0answers
400 views

No longer able to expand jobs in SQL Server Agent

In SSMS on a Windows 7 machine, while connecting to a server running SQL Server 2000, I am no longer able to click on the Jobs to see the Object Explorer Details. Previously, this was not an issue, ...
1
vote
0answers
187 views

Differential backup job is working but not the Full daily backup

I have a full backup job for my database that runs once daily, I see it posted daily in the folder that it should go to, but when I check the "View History" for this job in SQL Server Agent, there are ...
1
vote
0answers
3k views

How to debug problems with SQL Server multiserver (Master and Target server) administration?

I've tried to set Mater and Target Servers on various cobinations of SQL Servers 2012 R2 on Window Server 2008 R2 and Windows 7 machines but always getting one or 2 of the following errors during ...