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
43
votes
8answers
42k views

SQL Server Agent Jobs and Availability Groups

I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server ...
26
votes
3answers
75k views

Giving file system access to the SQLSERVERAGENT virtual account

I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in ...
22
votes
6answers
106k views

Could not find the Server Agent in SSMS

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8 Now to schedule your newly created script. ...
20
votes
4answers
65k views

What is the Query to display the failed sql jobs

What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query
18
votes
7answers
152k views

Script to see running jobs in SQL Server with Job Start Time

how to write script to see running jobs in SQL Server with Job Start Time? SELECT sj.name, sja.run_requested_date, CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) ...
17
votes
2answers
32k views

Get detailed output of SQL Agent job

We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose ...
16
votes
2answers
15k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/...
15
votes
1answer
10k views

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job ...
14
votes
4answers
14k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
13
votes
4answers
4k views

I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?

Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as ...
12
votes
2answers
92k views

SQL Job step run as different sql account

I'm curious how to set up a SQL job step to be executed as a different SQL login account. It appears I need to set up a New Proxy Account which requires an existing credential. When I create a ...
12
votes
2answers
17k views

Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some ...
11
votes
2answers
10k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
11
votes
1answer
20k views

Does SQL Server 2014 Express, with Advanced Services, actually support full text search?

I've installed SQL Server 2014 Express edition, with advanced services. I was hoping to try out the full-text search features. I read here that full text search is supported by the 2014 express ...
11
votes
2answers
2k views

alert when any job in a job category fails

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails? I am wondering because I would like to set up an e-mail anytime an SSRS ...
11
votes
3answers
3k views

SQL Server Agent fails after installing SQL Server 2019 CU2

After installing CU2 our SQL server agent randomly stops working. If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error: ...
10
votes
2answers
8k 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 ...
10
votes
4answers
52k views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE [dbo].[...
9
votes
2answers
16k views

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
9
votes
1answer
10k views

SQL Server Job History Disappears After 5 Days

I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being ...
9
votes
2answers
6k views

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name ...
9
votes
2answers
19k views

How to clear an Error 15404 after a DB restart (besides rebooting)

Every so often (e.g. ~months) a SQL Server Agent hourly Job will begin reporting an Error 15404 and continue to do so until intervened with. [298] SQLServer Error: 15404, Could not obtain ...
9
votes
1answer
654 views

SQL Server Agent initiating self-termination

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly: Sql Agent Output file [097] Memory leak detected [...
8
votes
4answers
4k views

Run Server Agent Job on Schedule only if CPU is idle

I have a job set up in SQL Server Agent to run an update query. I want this job to execute every night between midnight and 5am and I want it to repeat every 5 minutes during that time frame. But I ...
8
votes
2answers
13k views

How to see entire job step output?

We have a SQL Agent job running some PowerShell, which creates a pretty long output (it has robocopy output, among other things). In the SQL Agent Job History, we only see the first 1,000 characters ...
8
votes
2answers
11k views

Does the Sql Server Agent account need sysadmin permission?

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent ...
8
votes
1answer
2k views

How do I ensure only one instance of a job is running?

SQL Server 2014 Standard Edition. We have a job that runs from 20-40 min. We only want one instance of this job running, but we don't want huge gaps between runs. So, scheduling it every 60 min is ...
8
votes
2answers
4k views

How do Availability Groups work with cheduled jobs and SSIS packages?

I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group. I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run ...
7
votes
2answers
29k views

How to give SQL Server Job Owner permission to Proxy a Credential

I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password) I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM The job needs to proxy as credential Cred1 So when I ...
7
votes
2answers
13k views

What is considered for output from an sql server job step?

In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output? Background: Just executing a statement like UPDATE product SET ... will ...
7
votes
1answer
2k views

Renaming SQL Server Agent operator

I want to rename SQL Server Agent operator, that is change it's name. Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do ...
7
votes
1answer
8k views

SQL Server Agent logging actions and changes done to jobs

Is there a way to track changes done to SQL Server Agent jobs? If you had a reason to believe somebody is playing around with your job settings. How would you proceed to track who did it and from ...
7
votes
2answers
10k views

Execution-Policy Error running Powershell Script in SQL Server Agent

Running a powershell script from SQL Server Agent in 2014 using my AD account via a credential. I am getting the following error. A job step received an error at line 1 in a PowerShell script. The ...
7
votes
2answers
8k views

SQL Server Agent not observing “execute as” permissions

I have a curious permissions problem. Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working. They previously worked; ...
7
votes
1answer
2k views

What would be a good name for a job schedule?

I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive ...
7
votes
4answers
5k views

SQL agent job, why is the job history showing that a step is still running even though the job has completed

I have a sql agent job that runs Powershell as its first step (there are 3 steps in total). I have set this step to have 2 retries, with a 3 minute retry interval. When I look into the job history, ...
7
votes
1answer
2k views

some SQL Server MSX targets show as Normal, Blocked

I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window ...
6
votes
3answers
6k 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?
6
votes
1answer
677 views

Close button action when manually running a job

I started a job from SSMS on my laptop using Right Click > Start Job at Step... When I was ready to be done for the day the job was still running so I selected Close thinking it was operating on the ...
6
votes
5answers
669 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
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' ...
6
votes
5answers
11k views

Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields. I have a web interface that interacts with the sql scheduling system. I'...
6
votes
3answers
2k 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 ...
6
votes
1answer
530 views

Principle of least privilege for sql server agent jobs

I've just read this article which advocates using the sa account for ownership of sql server agent jobs. http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account He has a couple ...
6
votes
2answers
4k views

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the SQL Server Agent to see if it is running ...
6
votes
2answers
5k views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
6
votes
1answer
1k views

Passing current server name from SQL Server Agent job to PowerShell

I have a SQL Server Agent job that calls a PowerShell script in one step. And I want to pass the SQL server name and the current job-id to my script. With the job-id I have no problems. But with the ...
6
votes
2answers
6k views

Sql Server Agent notification email with custom message

I have set email notification on the completition of a scheduled job. Is it possible to customize the email content?
6
votes
2answers
13k views

Execute Remote Powershell script from SQL Agent

A test SQL Server job hangs when it executes a Powershell script. It does work using my login and a Powershell window, but not using the Agent account and a job. The SQL Agent user has read/write/...
6
votes
2answers
18k views

Unable to connect to FTP server using “FTP Connection Manager”

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log ...

1
2 3 4 5
11