Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...

0
votes
1answer
34 views

SQL AlwaysON. keeping Agent Job in sync, but with same JobID?

Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists? I have a software from a vendor that uses the Job ID to ...
0
votes
2answers
27 views

SQL Server Agent - Do not show job step details and column headers in output file

I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file (Right click the job --> properties --> advanced and then adding a file path to the output ...
0
votes
1answer
30 views

How to secure SQL Agent Job that INSERTS to a linked server

What is the proper way secure a T-SQL agent job that moves data between linked servers? Setup: Microsoft SQL Server 2012 (SP3) Enterprise Edition SQL Server Agent running as NT Service\...
2
votes
1answer
43 views

how to set up sql server alerts based on a schedule basis?

We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.
1
vote
0answers
17 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
34 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
29 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
34 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
35 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 ...
0
votes
1answer
49 views

Why do we need to use proxies for syadmin job steps?

I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin. I just don't get why we need to use proxies even the ...
0
votes
0answers
85 views

Running SSIS package as SQL Server agent job without installing Integration Services

I have set up a SQL Server agent job that runs an SSIS package stored as a File System (on a shared drive) on my SQL Server 2012 (remote) Production DB Server. I have created proxy account and am ...
0
votes
0answers
28 views

Executing stored proc in a SQL Agent Job fails on secondaries within a High Availability group with a sys.fn_hadr_is_primary_replica guard

I have a High Availability cluster of two servers. I have the same job setup on both servers within the cluster. The job has a single TSQL step, which is executed against master. The TSQL is as the ...
3
votes
2answers
453 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?
2
votes
2answers
57 views

DBATools - pass through flat file configs to Agent Job Commands

I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite. Given $sa = Get-Credential and foo.config of ...
0
votes
1answer
48 views

Log shipping - Job schedules

When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules? ...
0
votes
0answers
23 views

HA for Multi-Server Administration MSX - MSX to TSX

We have multiserver administration with a master server (MSX) to multiple targets. The MSX server databases are included in the Availability Group. We have a requirement to keep the MSX master as HA ...
0
votes
0answers
33 views

Is there any way to write to CloudWatch from an AWS RDS SQL Server Instance?

For scheduled tasks running against an AWS RDS server, I use a mix of apps living on an EC2 server and SQL Agent jobs. The apps can easily write success or failure to CloudWatch using the handy AWS ...
0
votes
1answer
76 views

CmdExec Powershell Job Step Escape Double Quotes

I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with ...
0
votes
1answer
41 views

SQL job ignoring IF condition

I have a job with a few steps(that run reports information) and i added a query i wrote to the last step. Sometimes the job fails because of factors i can not control (application issues) and my query ...
0
votes
0answers
41 views

Powershell Script in SQL Server Failing Due to Missing Method in System.__ComObject

I have a powershell script I'm executing as part of a SQL job and it is failing to write to the table. Running as CmdExec, here is my script. powershell.exe "Get-SQLServerUpdates -Version 'SQL ...
3
votes
1answer
199 views

INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache. As part of step 1 in the SQL job,i filter only the serial plans and insert it into a ...
0
votes
1answer
112 views

How to query Job Activity Monitor to get a list of all jobs

I am trying to obtain a list of ALL jobs (SQL Agent jobs). Specifically, I am trying to obtain the ID, name, status, LastRunOutcome, Last Run, Next Run, and most importantly description, owner AND ...
1
vote
0answers
170 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
60 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
1answer
42 views

Roles and Schduled jobs

I have some instances were some of the BI teams can create their own schduled jobs in the sql agent. However i also have a couple house keeping jobs i don't want them to interfere with. Can I create ...
0
votes
0answers
41 views

Is there a date modified timestamp for steps in a job on msdb?

I need to figure out which steps have been modified since the beginning of the month. I can find which jobs have been modified, but some of the jobs have 20+ steps, so knowing which specific step was ...
0
votes
0answers
119 views

Stored procedure runs fine manually but not as SQL agent job

I have a stored procedure that performs some data imports from a linked server. The linked server is on the same box as the main instance, it's just a different instance. The stored procedure runs ...
3
votes
1answer
145 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 ...
0
votes
0answers
9 views

When does a SQL Agent job split its output over multiple message rows?

If you look at the history of any given SQL Server Agent job that logs step output, you may see that it spreads this output over many rows. However, I can't seem to see any consistency as to when it ...
0
votes
0answers
78 views

Why does a scheduled SQL Server agent job fail when the proxy domain account is idle?

I have an SSIS package scheduled to run with an sql server agent using a proxy with my domain credentials. It imports data from a file dropped on a network drive that I have access to. The scheduled ...
1
vote
2answers
211 views

Trigger To Find Deleting Job Agent History

We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like: Create a trigger to check what application is ...
5
votes
2answers
306 views

SQL Server 2016 SQL Agent Token Not Working

I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..." But on my new SQL ...
0
votes
1answer
47 views

Archive backups with a PowerShell script launched by SQL Server Agent

I would like to run a Powershell script via the SQL Server Agent. This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a ...
0
votes
1answer
43 views

Job Activity Monitor - Log File Viewer is showing Duration for running jobs

As you can see in Log file Viewer it has a column Duration which change for a running job. I want to include this column in my query (from where I get jobname etc) Any idea to make a SIMPLE script? ...
0
votes
2answers
120 views

How do I automatically enable or disable report jobs at AOAG failover?

I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) ...
0
votes
0answers
70 views

SQL Job agent get running jobs, failed and canceld jobs in same query

I have 2 queries I want to combine into one, as below. I want to build on these queries due to restrictions I have as a user against the server. I can run exec sp_help_job @execution_status (it gives ...
2
votes
2answers
258 views

Domain Account or Windows account for Transactional Replication

All the online resources say to use a Windows account for Agent accounts, i.e. Snapshot, Distributor and Log Reader to set up Replication. See the following links as examples: Tutorial: Prepare SQL ...
0
votes
1answer
80 views

Multiple Agent Jobs Running same SSIS Package

I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example: 4 SQL Agent Jobs running the same SSIS in different schedules, I've ...
0
votes
1answer
49 views

SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently

I have a watchdog process that runs via a SQL Agent job to manage some custom tasks. One of the checks is that the process no longer exists: UPDATE R SET IsOrphaned = 1, DebugTimestampUtc = ...
0
votes
1answer
71 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 ...
3
votes
0answers
58 views

SQL server agent job runs fine when I schedule it while I'm at work but not if my credentials are not used in awhile

Sorry, I'm new to DBA but I need to automate some data ETL process (specifically .txt network file share drops). I installed SQL server 2014 on a windows 7 PC with SQL Server Agent, and I'm running a ...
1
vote
1answer
73 views

SQL Server Agent Service deleted

Has anybody run into the issue where an application (arcserv) using SQL Server Express 2008 is uninstalled on a server running SQL Server 2014? When arcserv was uninstalled, it or user deleted the SQL ...
1
vote
3answers
1k views

SQL Server Agent Server Login Account

What SQL Server login does the SQL server agent use to log in to the database instance? I am running the SQL server agent as the built in local system account. I have created a SQL agent job which ...
0
votes
0answers
152 views

SSIS package Job problem

I have an SSIS package that loads data nightly from a warehouse to a database. Both the SSIS and SQL Server agents are on SQL server 2012 Enterprise and Windows Server 2012 R2 Standard. When I run ...
2
votes
2answers
473 views

View SQL Server Agent jobs -> MSDB roles

When attempting to view the jobs' properties in SSMS, the users would get the error execute was denied on the object sp_help_targetserver. This is a sys SP in MSDB. The users have ...
2
votes
1answer
2k 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
0answers
47 views

Setting precedence of MS SQL Agent alerts

I've recently encountered an issue with a SQL system I've inherited. Agent alerts have been configured to trigger on Severity and on Error ID. However, it seems like a given error can only trigger a ...
1
vote
1answer
71 views

Measure Agent Job failure and running jobs with 'execution_status'

Considerations I need to create a Dashboard with job failure AND running jobs. I have a code to get run_status etc. I am not interested in succeed jobs. I extract failure from run_status = 0. ...
1
vote
3answers
2k views

How to send Email from a SQL Job with results of multiple select statements

I want to create a SQL job and send an emails with multiple select statements including in query results. Is it possible with SQL Job or should I use SSIS? Edit 1: EXEC msdb.dbo.sp_send_dbmail ...
0
votes
0answers
523 views

SQL Server Agent job fails to run SSIS package, but the package runs fine manually

SQL Server Agent job fails to run SSIS package, but the package runs fine manually The SQL server is SQL Server 2012. The job has been working for a long time fine. This is the first time when it ...