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
2
votes
0answers
66 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
3k 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
589 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
1k 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 ...
6
votes
1answer
3k 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' ...
2
votes
1answer
983 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
88 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 ...
5
votes
2answers
4k 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
1k 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 ...
4
votes
3answers
74k 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
78 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
7k 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
2k 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
723 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
4k 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
80 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
53 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 ...
1
vote
1answer
28k 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 ...
2
votes
1answer
280 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 ...
3
votes
7answers
6k 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 "...
3
votes
3answers
6k 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
2k 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 ...
4
votes
2answers
5k 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 ...
3
votes
0answers
46 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 ...
3
votes
2answers
4k 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
2k 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
122 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
3k 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
83 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
619 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
268 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
396 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
5k 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
150 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
1k 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
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
218 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
278 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
3k 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
2k 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
569 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
548 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
2k 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 ...
4
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
3 4
5
6 7
11