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
3
votes
2answers
4k views

SQL Server Agent Invokes Failed 'sa' Login

I recently upgraded to SQL Server 2016. I was looking through the SQL Server logs and noticed the following error. Login failed for user 'sa'. Reason: Password did not match that for the login ...
2
votes
0answers
995 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 ...
1
vote
1answer
595 views

Updating Agent Job's Next Run Value

Every so often, my company's BI team will request the today's execution of a job be skipped. What I've been doing in the past, is going into the job schedule and manually changing the schedule's ...
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 ...
1
vote
2answers
73 views

SQL Agent not Running

Please Help. I have been trying this for the past 3 days. I need to send an alert mail when the SQL Server Agent is not running. That is, there can be instances where SQL Server is is running, but ...
3
votes
2answers
44 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 ...
0
votes
1answer
18k 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
1answer
201 views

SP_whoisactive capture on Alwayson Secondary Replica issue

I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column. This happens when I run the code below as a ...
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 "...
2
votes
2answers
3k views

Replication Access denied in Replication

I have a database on-prem, and I have an Azure account. I need to establish transactional replication from the on-prem to Azure. First of all I created a publication and after that I made a ...
2
votes
1answer
2k views

SQL Agent Job step using Powershell can not find Network Share

I have a SQL Job step running powershell that cannot find a network drive in one of its steps For brevity, I am calling this server Production, the network share is D:\ on Production and the Job is ...
0
votes
1answer
1k views

How to check decimal separator in T-SQL

How can I check with a T-SQL Command what decimal separator is in use in a given user context. Microsoft Windows Server 2012 has a bug where the regional settings for Swiss German introduced an ...
3
votes
2answers
4k views

Automated SP_WhoIsActive SQL Activity Capturing Issue

I have an SQL Agent Job on a production server that keeps failing with the below messages. It is supposed to be capturing SQL Server activity using the SP_WHOISACTIVE stored proc, at regularly ...
2
votes
0answers
31 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 ...
4
votes
1answer
1k views

Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS

I have two T-SQL scripts that are scheduled to run once every day. Both do cleanup of some old data from different tables. Both scripts are run as the same user, on the same database. Script A will ...
2
votes
2answers
3k views

SQL Server 2014 SQL Job CmdExec and access denied

I have a little trouble with SQL Agent Job. It should execute program in network directory like \\x\y\z.exe. Job Step uses proxy account with credentials of a user, which can full access that ...
0
votes
1answer
1k views

Check if file exists in step in SQL

Need to create step in SQL job which check if file exist because if file exists it needed to be stop but if file not exist to continue with next step. xp_fileexist not allowed in sql query.
0
votes
1answer
91 views

SQL server Installation - SQL server agent is enabled by default?

When SQL server is installed on a machine, is the SQL Server Agent enabled and running by default, or does it need starting manually ?
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
1answer
81 views

SSMS 2016 Job Schedule Wizard Starting At entry field

I am using SSMS 2016. In the wizard for job schedules the field for Starting/Ending At shows only one character, and is unusable. Here is the screen shot. SSMS version should be the latest at 13.0....
0
votes
3answers
474 views

Who started the SQL Server Agent? [closed]

How to find out who started the 'SQL Server Agent' in Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)
0
votes
0answers
258 views

Which SQL Server Agent job is processing my tabular model database

I have a server called SANDBOX with two SQL 2014 instances SANDBOX\00 and SANDBOX\01. It also has a tabular model instance SANDBOX\TABULAR. Agent jobs are disabled on SANDBOX\01. There is no agent (as ...
0
votes
1answer
303 views

when scripting SQL server agent jobs, is it possible to use @@servername in the @command settings line

I am trying to script an SQL server agent job so that one script can be run on multiple servers to set up the same job. we have the same users and will use the same job name on the different servers. ...
1
vote
2answers
3k views

.bat file works manually, but does nothing in SQL Server Agent job

I am able to run a .bat file that copies an .exe file into a specific folder, and runs it to extract some files manually. When I use it as part of a SQL Server Agent job (step 1), I get success for ...
0
votes
2answers
139 views

How to convert a variable to be used in where clause

I have stored Job_id value of msdb..sysjob in a variable @job_id_char, which i want to use against msdb..sysjobactivity to get the 'start_execution_date' value. I have used following query but getting ...
1
vote
0answers
694 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 ...
0
votes
1answer
1k views

SSISDB Transaction Log Backup

I have several SSIS packages jobs running, and some months ago my disk got full because the size of the SSISDB database. I noticed that the cleanup_server_retention_window was set to 365 days, and I ...
0
votes
1answer
174 views

How to create user for existing

I have a database which get restored everyday through a agent job, but after restoration the user gets killed so i want to create a step so that after restoration user should be created with readonly ...
0
votes
1answer
237 views

Does using Retry attempt option SQL Agent job create performance issue

I want to use Retry attempt option of sql agent job, want to know does it hold the resource and will create performance issue, if i keep it every 60 minutes
0
votes
1answer
1k views

is there a way to run the same job multiple times without scheduling it?

I have to run a process, and it says: --first see what is the situation with the job EXEC msdb.dbo.sp_help_job @job_name=N'Create new gift vouchers' EXEC msdb.dbo.sp_start_job N'Create ...
2
votes
1answer
2k views

SQL Server job to use external Powershell

I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the ...
1
vote
3answers
1k views

How to know if backup file is available before trying to restore

I have a restore job, but i want to add a step before restore step to know if backup file available.so that it should go to restore step only if backup file available.Please let me know a query which ...
1
vote
0answers
386 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 ...
0
votes
1answer
442 views

Always On: SQL Agent can still run stored proc on inactive server

We have the AO Clustering - HA&DR Hybrid configuration. Out of a dozen databases only this one database/application is encountering this. The problem is that we have a SQL Agent Job scheduled to ...
2
votes
1answer
2k views

How can I get the actual SSAS error when processing a cube from a SQL Agent job?

I have a SASS cube on one server and a SSIS package on another server that triggers processing the cube. The SSIS package is executed via a scheduled SQL Agent job. I have enabled error reporting for ...
0
votes
1answer
1k views

Update empty values with null

I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed ...
1
vote
2answers
70 views

SQL Server 2012: Monitoring SPID on unpredictable app

Techies, There's an application @my shop that gets executed through sql agent which works 95% of the time. The job completes within 10 minutes. The other 5% of the time, this same job stays active ...
3
votes
3answers
1k views

How can I limit the number of emails sent by SQL Server agent?

I use SQL Server agent notifications a lot for notifying of failed jobs. Usually, jobs run every hour so I don't mind getting a few emails if the job fails. However, I want to run a job every minute ...
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 ...
2
votes
0answers
242 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 ...
3
votes
1answer
408 views

Changing the Owner of Replication Jobs

Just wondering if there is any gotchas with changing the owner of a sql server replication job. The job owner is currently a domain account, but that person is leaving the company, so the account is ...
0
votes
0answers
454 views

Run SQL Agent Jobs in Sequence

While there are other questions out there in forums, I notice that those are several years old, and I hope that things have changed by now. I am using SQL Server Management Studio 2014 and I have to ...
2
votes
0answers
381 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
1answer
501 views

Scheduled jobs run twice after upgrade to SQL Server 2012

We recently upgraded from 2008 to 2012, with one of our applications running on an active & passive server (Always On). It has been running SQL Server Agent jobs twice at the exact same time. We ...
2
votes
2answers
5k views

Getting SQL Server Agent to run multiple instance of a job at a time

As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time. I need it to Start a job 15 times, and then let them all run at the same time. Up to now, ...
0
votes
0answers
416 views

Is it possible to configure exceptions for SQL Server Agent tasks?

I use SQL Agents tasks to execute my backup, maintenance & ETL tasks. IT would like to have a regular maintenance window to apply server patches. They're suggesting the 3rd Tuesday of every month ...
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, ...
2
votes
1answer
1k views

SQL Server Agent job returns a failure status message after the job succeed.

We have a sql server agent job which runs every day on sql server 2008R2. The job has been running for years and had no issues before; and nothing has been changed recently. Two weeks ago, the job ...
0
votes
0answers
3k views

Failed to execute IS server package because of error 0x80131904 … System.ComponentModel.Win32Exception: The handle is invalid

I have an SSIS package that is executed every 30 seconds by a SQL Agent Job. Every few days, an execution will fail with this message in the Job History: Failed to execute IS server package because ...
2
votes
1answer
313 views

Prevent SQL Job Agent from interpreting a quotation mark as apostrophe

I'm struggling with my SQL Job Agent interpreting a quotation mark " as an apostrophe '. I have a T-SQL query which works in SQL Server Management Studio but not when I insert the exact same ...