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
1
vote
1answer
426 views

SQL Agent reporting failure when all steps suceed

The SQL Agent is reporting failure when all the steps under it has succeeded. Anyone else seen this behavior ?
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: ...
0
votes
1answer
537 views

AlwaysOn - Running SQL Agent Jobs on Secondary read-only node writing audit information to database

I am setting up an AlwaysOn Availability Group with 3 nodes. 1 Primary, 1 Secondary Read-only node and 1 secondary non-readable node. I want to offload my maintenance jobs to my secondary read-only ...
1
vote
1answer
5k views

SQL Server 2016: create credential and add SQL Server Agent proxy for it

I'm trying to script a creation of SQL Server identity to be then used to execute SQL Server agent jobs via a proxy. It looks as though I can only use a Windows account and thence I would have to ...
1
vote
1answer
435 views

Change logging level programmatically for multiple jobs

I have multiple Sql Agent jobs, with multiple steps each. For a large subset of these jobs, I want to set the logging levels to PERFORMANCE instead of BASIC - the logs are taking a lot of log space, ...
0
votes
0answers
353 views

SQL Server Agent - remotely starting jobs from machine outside AWS fails with Semaphore Timeout

Starting the same job using SSMS on the machine directly always works. Can't figure out why. Is there some firewall rule I need to allow? It works via SSMS when logged into the box directly. It does ...
1
vote
1answer
38 views

Can I use Default Accounts for setting up AOAGs 2014\2016

Can anyone help me on below : I would like change the SQL Service \ SQL Agent Service to Active Directory Accounts . But before that I would like to understand below : Can I use the default accounts ...
2
votes
1answer
402 views

SQL Job Step timeout

If a SQL Agent job step executing a stored procedure on a linked server times out, does the agent kill the SPID on the linked server or does it continue to run on the linked server?
0
votes
1answer
113 views

SQLAgent executing command on Mirror database

I have a mirrored database DB1, ServerA as primary and ServerB is mirror. I have a job running on ServerB (mirror), configured to run against DB1 database. I was expecting the job to fail but the ...
1
vote
1answer
203 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 ...
1
vote
2answers
508 views

SQL Job is getting failed randomly while trying to send the alert email. need help

We have configured a alert which triggers the email using a job. But the job fails randomly during the day and we don't receive the alert related mails. If I clear the ExternalmailQueue it starts ...
4
votes
1answer
280 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, ...
2
votes
2answers
734 views

Is SQL Server Mirroring dependent on SQL Server Agent running?

I have SQL Server mirroring set up between two 2008R2 SQL Server instances. I have multiple databases mirrored. Prior to planned failover to the backup server I want to turn off the SQL Server Agent ...
3
votes
1answer
6k views

Missing assembly when running PowerShell script using SMO

I wrote a script which takes database name, restored DB name, backup source, data file location and log file location as parameters and performs database restore on SQL Server instance. Script works ...
1
vote
1answer
599 views

Cannot process cube using SQL agent job in SSMS

I have an issue where we have a user who has used his own AD account to create and deploy the cube back when he was the sole DBA. I would like to have an proxy account with a password known ...
0
votes
3answers
70 views

Hi folks, In one of my Agent Job is running by every minute

One of my SQL agent Job is running every minute, the job has failed . I need the job history of past five days. Is there any way we can get the history details? I have searched in the log but not ...
0
votes
2answers
2k views

Need to setup a sql alert on “waiting tasks” - similar to “waiting tasks” in Activity Monitor

My manager has asked me to work upon a request. I am not sure if it is reasonable or weird but he wants me to setup an alert based on the "waiting task" that we see in the activity monitor. Basically, ...
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
1answer
1k views

SQL job using SSIS package takes a long time to run especially in the evenings

I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so ...
3
votes
1answer
483 views

Query job history for TSX servers from MSX server using TSQL

I am attempting to construct TSQL queries to substitute various GUI tools provided by SQL Server Management Studio. One of these tools is the Job Activity Monitor, accessible through the Object ...
3
votes
1answer
2k views

SQL Agent Job Logs - Can't get the Error Log

I've some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to ...
6
votes
3answers
1k views

SQL Agent PowerShell Syntax Error

I am attempting to setup a PowerShell script within a SQL Agent job (SQL Server 2016). I'm using a third party module, DBATOOLS.psd1 which appears to be where I'm getting my syntax error. Currently my ...
0
votes
1answer
458 views

How to setup Email alerts for Reporting jobs when it fails?

In SQL server 2008R2, I have a JOB which is running every month 1st week for Reports and I am frequently receiving Error message like "The job failed. The Job was invoked by Schedule 14. The last ...
3
votes
2answers
3k views

How to capture error message from one step in another step of an Agent job?

Is it possible to capture error message from one step in another step of a SQL Server Agent job? I have below scenario: Step 1: Transact-SQL script (TSQL) -- On Success action: Go to step 2 -- On ...
0
votes
0answers
701 views

SQL Server job with powershell script fails but the script works fine in Windows powershell window

I have setup a SQL Agent job to delete files older than 7 days. The script does it job when run through windows powershell window. However the same script does not work from SQL Agent job $...
0
votes
1answer
420 views

Unable to start the SQL Server Agent

I am unable to start then SQL Server Agent. I am getting the below error message when I tried to start the agent: The SQL Server Agent service (DB_NN) on local machine was started and then stopped....
2
votes
2answers
435 views

sp_add_jobstep add more than 4000 Character in @command parameter

sp_add_jobstep is well documented here. I understand parameter @command is type NVARCHAR(MAX). I am creating a SQL Agent Job using a Stored Procedure. Passing the value for @command parameter via a ...
3
votes
1answer
1k views

SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher [closed]

SQL Server 2016 SP1 Enterprise Edition Data Engine (with SQL Agent) installed on an FCI called inst1. SSIS 2016 SP1 Enterprise Edition installed on comp2. (comp2 is not in the inst1 cluster) ...
1
vote
2answers
1k views

SQL Server agent job question

Scenario: I am trying to understand a strange situation. I have a SQL Server 2012 having DB engine and SQL Server agent configured using same domain account. That domain account isn't added to SQL ...
2
votes
0answers
150 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 ...
1
vote
2answers
290 views

sql agent job notification email without configuring database mail

Is there any way to send sql agent job notification email without configuring Database Mail? Currently I am using Database Mail but due to security concern, i need to disable Database Mail feature. ...
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 ...
3
votes
3answers
2k views

Troubleshooting Powershell errors from SQL Agent job

I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error A job step received an error at line 1 in a PowerShell script. The ...
1
vote
1answer
494 views

Unable to Restart the SQL server Agent from SSCM [closed]

In my machine SQL Server Agent is not expandable with a label (Agent XPs disabled). In this case I run this code to enable it. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO ...
2
votes
2answers
583 views

Enable Job instantly after AOAG Role change

We have a archival job which is set up in all three replicas which are part of AOAG (SQL Server 2014). Here the connection string is AOAG Listener (which connects to only Primary Replica). I have ...
5
votes
1answer
2k views

Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?

Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript. The error message suggests that 'ACTIVESCRIPTING' ...
1
vote
1answer
592 views

Suppressing non-ANSI warnings in SQLCMD script

I am working with a script that creates a SQL Agent job in SQL Server 2014. The job's last 2 steps are "Complete with Success" and "Complete with Failure". The script always warns of non-existent ...
0
votes
1answer
2k views

Cannot see SQL Agent Job history for more two days

I have a job which runs every night around 02:00 AM. But I cannot see the job history for more than two days.Can you kindly help me on this. The limit size is very high and we hardly have one job ...
0
votes
1answer
78 views

Clarification — Disabling SQL Server 2014 Agent Job in AOAG Secondary replicas

We have AGs set up (1 primary and 2 secondaries (Includes DR)). We have an archival job set up in each replica with same schedule and enabled in all three replicas. This job runs in a frequency of ...
4
votes
2answers
2k views

Adding SQL Agent output to text file for all agent jobs in one go

As a best practice, i am trying to schedule an activity where all SQL agent jobs should log output to text file. Manually i can do this for single job. But i am looking for a better way either by ...
1
vote
1answer
482 views

SQL Server 2012+ Agent Job with Parameter to determine job flow

Is it possible to have an Agent Job with an input parameter? What I'm looking to do is have a flag to determine how the job flow will occur. For example: I have a 10 step job. Steps 1-5 are always ...
2
votes
3answers
40k 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 ...
1
vote
1answer
56 views

Disable TCP Connections in SQL Script

We have a SQL Server Agent job for loading dumps in case we need to restore from our daily backups. The first step in this job is supposed to kill all connections to the database. However, we ...
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
943 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
565 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
72 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
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 ...
0
votes
1answer
17k 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 ...