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 ...
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.7k
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 ...
- 70.5k
13
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....
- 70.5k
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
...
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 ...
- 18k
12
votes
How can we prevent Agent Jobs running twice when the clocks change?
Use UTC when setting up your SQL Server.
If the platform you're serving is large enough that this is an issue, then you're probably doing business in more than one time zone. If you never plan on ...
- 5,001
11
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 ...
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 ...
- 10k
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 ...
- 162
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:
...
- 47.7k
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.
8
votes
How to give SQL Server Job Owner permission to Proxy a Credential
Check select * from msdb.dbo.sysproxies if your proxy is enabled, we had a similar problem and we recreated the proxy with enabled = 1.
That seem to have solved the problem with this error.
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 ...
8
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,...
- 62.4k
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.5k
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 ...
- 18.2k
7
votes
Does changing database growth rate force a new execution plan?
I am just curious what has happened internally within SQL Server that would have resulted in quicker run times.
When a query is running, and the transaction log file is required to grow, a bunch of ...
- 29.8k
7
votes
Accepted
Why does this query fail when I run it from a SQL Server job?
I'd say the problem is related to the language being used when the query runs.
Please, run this from SSMS and from the job you created to check if the language being used is the same on both methods:
...
- 5,926
7
votes
Can AWS services replace SQL Server Agent Jobs?
Can AWS services replace SQL Server Agent Jobs?
Sure, in the same sense that Walmart can replace McDonald's. Both sell food, but one of them requires you to cook the food yourself and it may not come ...
- 39.5k
7
votes
How can we prevent Agent Jobs running twice when the clocks change?
Don't start jobs between the times when clocks change in your location.
For example In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday ...
6
votes
Failed to notify 'DBA Team' via email
Ensure you've enabled Mail Session in SQL Server Agent.
SQL Server Agent > Properties > Alert System: Check Enable mail profile.
This link should help:
Avoiding failed notified operator email ...
- 61
6
votes
How to send Email from a SQL Job with results of multiple select statements
You are basically sending 1GB file from sql server which is max. You can reconfigure the default using
exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','2000000'-- 2GB
I would say, use bcp out ...
- 62.4k
6
votes
SQL Server Agent Service deleted
SQL Server 2008 Express shouldn't have a SQL Server Agent, so I'm assuming that the 2014 Agent is what was uninstalled.
I would run a repair from the SQL Server 2014 install media, not the arcserv ...
- 167
6
votes
Accepted
Start job from another SQL server instance
You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK ...
- 69.5k
6
votes
Accepted
Schedule Full, Differential and Transactional Backup in SQL Server 2016
Adding to the answer of Shekar Kola.
Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure.
If you're ...
- 1,783
6
votes
Accepted
Why the job's next_run_time is wrong?
The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.
NOTE: The sysjobschedules table refreshes every 20 minutes, which may
affect the values returned by the ...
- 16.5k
6
votes
Accepted
SQL Server job stuck even after restart
Not using the GUID but using the T-SQL procedure:
sp_stop_job
Could help resolve issues like the one you are having.
Example:
exec msdb.dbo.sp_stop_job @job_name = 'enterjobnamehere'
Checking ...
- 16.5k
5
votes
Giving file system access to the SQLSERVERAGENT virtual account
I agree frustrating.. apparently it's as simple as just typing the name.
http://zarez.net/?p=3187
Summary: Type NT SERVICE\MSSQLSERVER and then don't click check name just hit OK and you are able to ...
- 61
5
votes
Automated SP_WhoIsActive SQL Activity Capturing Issue
It appears from the Primary Key name that you are running Adam Machanic's sp_WhoIsActive and storing the result in a table.
If that is the case, the warnings are normal and caused by the options you ...
- 423
Only top scored, non community-wiki answers of a minimum length are eligible