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
1answer
51 views

SQL Agent Jobs hanging at last Run date and time

For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done. When I enable the jobs via TSQL ...
0
votes
1answer
49 views

Sizing the SQL Agent Job Activity Monitor screen

Does anyone know how to reset the initial (default) window size that is used when opening the SQL server Agent Job Activity Monitor screen? When opening the Job Activity Monitor we always need to drag ...
1
vote
0answers
287 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 ...
0
votes
2answers
139 views

How to have SQL Server's SQL Agent pick up a new Powershell module

I have a Powershell module that I've written and uploaded to my SQL Server at C:\Program Files\WindowsPowerShell\Modules.I have a SQL Agent job with a powershell step that calls the module. This all ...
0
votes
1answer
66 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
537 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
53 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
126 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
21 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
244 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
62 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
154 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
330 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
551 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 ...
5
votes
3answers
2k 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
106 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
230 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? ...
-3
votes
1answer
60 views

SQL Server Backup does not run

After changing the sa password of SQL server 2012, the automatic backup doesn't run any more. Please see the log history: The backup was not implemented by me. Login failed for user 'sa'. ...
0
votes
1answer
380 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
65 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 ...
4
votes
1answer
2k 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
1k 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
922 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
116 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
143 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
95 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 ...
3
votes
2answers
566 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 ...
1
vote
2answers
534 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
1k 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
73 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
168 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? ...
1
vote
2answers
562 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) ...
2
votes
2answers
1k 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
284 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
74 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 = ...
-1
votes
1answer
316 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
1answer
238 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
6k 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
325 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
2k 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 ...
4
votes
1answer
9k 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
52 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
164 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
4k 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
1k 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 ...
0
votes
1answer
3k views

SSIS packages fail with Pending Execution Status

My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status. All ...
2
votes
1answer
56 views

Setting Up SQL Alerts

I have turned on notifications via email on my SQL Server Agent if any of my scheduled jobs failed or is successful. Can anyone help me if I want to set up Alerts, if in case one of the scheduled job ...
7
votes
2answers
4k 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 ...
0
votes
2answers
4k views

T-SQL: How create job that run monthly

I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL. I know how to schedule a job daily at a specific time: ...
2
votes
1answer
700 views

SQL Server Agent/Properties/Alert System/Mail Session/Test is disabled

In order to enable operator notifications I navigated to SQL Server Agent/Properties/Alert System/Mail Session, checked "Enable mail Profile", selected Mail System and Mail Profile. At this time Test ...