25
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 ...
- 2,738
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 ...
- 61.6k
18
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 ...
- 722
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:...
- 15.5k
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 ...
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....
- 68.5k
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 ...
- 15.5k
13
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 ...
- 68.5k
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 ...
- 41.7k
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
...
- 36.7k
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 ...
- 16.1k
11
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....
- 7,248
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 "...
- 7,069
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 ...
- 9,732
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 ...
- 164
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:
...
- 43.9k
9
votes
Accepted
Creating a global temp table in a SQL Agent job
The difference between global and local temp tables isn't how long they exist, it's who can see them.
Local temp tables can only be seen by the session (spid) that created them. Global temp tables ...
- 3,796
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 ...
- 178k
9
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 ...
- 67.4k
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.
- 23.5k
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 ...
- 15.5k
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 ...
- 11.2k
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 ...
- 23.5k
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....
- 16.2k
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 ...
- 67.4k
7
votes
Accepted
Export SQL Agent jobs with steps and scripts
Would something like this work?
USE [msdb]
GO
SELECT
j.Name
, j.[Description]
, js.Step_ID
, js.Step_Name
, js.Database_Name
, js.Command
--, j.*, js.*
FROM dbo....
- 189
7
votes
SQL Server alerts not sending notification emails to the operator
Check SQL Server Agent Properties: on the "Alert System" page, is "Enable mail profile" checked?
Sometimes with new installations, after I set up options on that page I've found notification emails ...
7
votes
Accepted
Domain Account or Windows account for Transactional Replication
Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure.
Your confusion here ...
- 18k
7
votes
Is there a way to back up agent jobs?
Another way is to just export to file using dbatools
Get-DbaAgentJob -SqlInstance workstation\sql2016 | Export-DbaScript
you can even script out your entire instance or subset including agent jobs,...
- 61.6k
Only top scored, non community-wiki answers of a minimum length are eligible