59 votes

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

I posted a query a while back for getting a list of currently running jobs here. SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 ...
user avatar
24 votes

Giving file system access to the SQLSERVERAGENT virtual account

To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue: 1.> Right-click ...
user avatar
  • 2,708
21 votes
Accepted

Get detailed output of SQL Agent job

Is there any way to extract the full text of these messages? You can achieve it in 2 ways - Go in job step and select the Advanced tab: a. Output to a file (<== My preferred method) b. "Log to ...
user avatar
  • 61.2k
17 votes
Accepted

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

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012. The only thing that actually matters is: Server:...
user avatar
  • 15.4k
16 votes

SQL Server Agent Jobs and Availability Groups

Rather than doing this on a per job basis (checking every job for the state of the server before deciding to continue), I've created a job running on both servers to check to see what state the server ...
user avatar
  • 662
16 votes
Accepted

What is the best way to include SQL Server Agent jobs in Source Control?

I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database. I wanted the solution file ...
user avatar
14 votes
Accepted

Close button action when manually running a job

The "close" button simply closes the dialog; it does not stop the running job. You can use the following stored procedure to see the current runtime state of a given job: EXEC msdb.dbo....
user avatar
  • 67.2k
13 votes
Accepted

Execute permission denied on object sp_start_job

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some ...
user avatar
13 votes
Accepted

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

The default for QUOTED_IDENTIFIERin SSMS is ON while it is OFF for SQLCMD, OSQL, BCP and in your case especially SQL Server Agent. For reference, these are the different default options per ...
user avatar
  • 15.4k
12 votes
Accepted

How to see entire job step output?

In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename. That should include all of your output for later viewing. Or, if you want all ...
user avatar
12 votes

Execute Sql statement and send result to email

Your code is backwards. You are sending the email, then generating the query results. The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the ...
user avatar
  • 67.2k
12 votes
Accepted

INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

It's not the way the table was created, it's the options your query runs with. Agent does indeed run with the wrong settings. SELECT s.* FROM sys.dm_exec_sessions AS s WHERE s.is_user_process = 1 ...
user avatar
  • 33.5k
12 votes

Why does this query fail when I run it from a SQL Server job?

It fails because you aren't using a language neutral datetime format. You are assuming that SQL Server understands that the day comes first, then the month and finally the year. When you run this ...
user avatar
10 votes
Accepted

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

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012. First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at: ...
user avatar
  • 13.8k
10 votes

What is the Query to display the failed sql jobs

You can get this information by the standard reports available in sql server: Make a right click on SQL server agent> Standard reports and select the desired report like job executions "...
user avatar
  • 6,970
10 votes
Accepted

Is there a way to back up agent jobs?

SQL agent jobs are stored in the system msdb database, so backing up that database will do what you need. In a disaster, you can restore the entire msdb database, or, if necessary, recover individual ...
user avatar
  • 9,614
10 votes
Accepted

SQL Server Agent fails after installing SQL Server 2019 CU2

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will ...
user avatar
10 votes
Accepted

Suppressing SQL Server Management Studio's 'Messages' from output of SQLCMD via SQL Server Agent

A better approach is to switch over to a PowerShell job step, which is about 1000% more powerful than CMDEXEC. For instance in PowerShell writing an XML (or JSON) result to a file is as easy as: ...
user avatar
9 votes

SQL Server Agent Jobs and Availability Groups

I'm aware of two concepts to accomplish this. Prerequisite: Based on Thomas Stringer's answer, I created two functions in the master db of our two servers: CREATE FUNCTION [dbo].[svf_AgReplicaState](...
user avatar
  • 201
9 votes
Accepted

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

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance ...
user avatar
9 votes
Accepted

SQL Server - cannot drop idle job

You have maintenance plans. Try to follow these steps: Find the maintenance plan name and id that you want to delete. Write down the id of the one you want to delete. SELECT name, id FROM msdb.dbo....
user avatar
  • 7,114
9 votes
Accepted

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

Discontinued Database Engine Functionality in SQL Server 2016 indicates ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.
user avatar
8 votes
Accepted

Stop agent job after executing for a certain number of hours

Personally I would create another job and schedule it to run each morning at 7am. A job step within that job could execute something like USE msdb ; GO EXEC dbo.sp_stop_job N'<Your job ...
user avatar
  • 1,437
8 votes
Accepted

What does N'[Uncategorized (Local)] mean?

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC. See the sp_add_category documentation: [ @name = ] 'name' The name of the category to be added. The name must be ...
user avatar
  • 15.4k
8 votes
Accepted

Principle of least privilege for sql server agent jobs

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored. The least privilege you need to perform database maintenance is sysadmin. Lower privileged ...
user avatar
  • 10.9k
8 votes
Accepted

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

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it ...
user avatar
8 votes
Accepted

Missing assembly when running PowerShell script using SMO

Using some information found in SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell, I was able to get past that error. Try adding the following to the very top of ...
user avatar
8 votes
Accepted

Change character limit of SQL Agent Operators

Just because you can does not mean you should. Please do not apply the changes below. This answer is only to show you why you should not change system tables or system procedures and what can go wrong....
user avatar
7 votes

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

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? NONE :-) Starting in SQL Server 2005, Microsoft provided a mechanism to infer ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible