33
As of PostgreSQL 9.5, you can use the pg_cron extension, which is loaded as a shared library into PostgreSQL.
After setting it up, creating a job is pretty simple:
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
This will run the delete command according to the specified cron schedule. You can ...
19
It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own.
EDIT: For clarity on the three options presented by explicitly mentioning the SQLAgentOperatorRole as an option and by adding some explanation on the third solution.
(1) If the user is allowed to manage the execution of all jobs, ...
15
Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename.
Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection ...
12
You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing.
I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the ...
12
Late answer but could be of use to other readers.
Please, have in mind that there are lots of maintenance or reporting tasks, you can create, that carry unseen risks associated with them.
What would happen when a drive gets filled up during differential backups performed daily? And what if an index rebuild job runs unusually long? How about if a data load ...
12
Create a job that is scheduled to start every minute. Have the job do something like:
WHILE 1=1
BEGIN
EXEC dbo.SomeProcedure; /* this would be the
name of a stored procedure that does the
actual work */
WAITFOR DELAY '00:00:03.000';
END
11
This is part of the job step definition and you may even see it have values used or defined in other areas.
After taking a look in the source code (I work at Microsoft and have access), while the value is indeed passed as part of the job step information sent to each subsystem, I couldn't find a place that actually set the value as part of the job step ...
answered Dec 22 '16 at 19:28
Sean Gallardy - Mostly Retired
21.4k2 gold badges34 silver badges65 bronze badges
11
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 *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND ( ansi_nulls = 0
OR ansi_padding = 0
OR ansi_warnings = 0
OR arithabort = 0
OR concat_null_yields_null = 0
OR ...
10
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 "failed ones or the ones taking lot of time" ..Select the report as per you're need:
Or,
You can use below script to find jobs failed in last 24 hours:
-- ...
9
There is no log for scheduled events, unless they throw exceptions... in which case, you'll find an error written to the standard MySQL "hostname.err" error log. If there isn't an error, and they aren't still running, then they processed. Each event gets a new thread id in the processlist each time it fires and the thread is destroyed when the event is ...
9
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 won't start, because an instance of the job was already running).
9
Discontinued Database Engine Functionality in SQL Server 2016 indicates
ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.
answered Apr 11 '17 at 14:22
Scott Hodgin - Retired
23.1k2 gold badges20 silver badges42 bronze badges
8
Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours.
SELECT
job_name = name,
avg_sec = rd,
avg_mmss = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
SELECT
j.name,
rd = AVG(DATEDIFF(...
8
There are two possible ways
1. Automatically create scripts for all SQL Server Jobs
I. Open Object Details
II. Select All Jobs (Shift + Mouse Click) -> Right Click -> Script Job as
8
If there is no read that happens prior to the first write then it is said to be a blind write.
Example. Consider the following schedule:
W3(X) is a blind write, as there is no read before write [R3(X) before W3(X)]
W2(X) is not a blind write, as a read happens before write [R2(X) before W2(X)]
8
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 is only the PRINT / RAISERROR messages that are included.
Try this test:
Job Step 1
Definition:
PRINT ' ** Line 1 ** ';
SELECT ' ** Line 2 ** ' AS [Line ...
8
The problem with your first query is the use of unescaped brackets with the LIKE operator.
When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular ...
7
An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys....
7
I have managed to find one solution to this, although it's not as neat as I would have preferred. I've created a second job that checks the last run date on my target job. This is scheduled to run daily, a couple of hours after the target job should have run.
If not exists
(
SELECT 'x'
FROM msdb.dbo.SysJobServers S
INNER JOIN msdb.dbo....
7
You can't disable a particular job step.
You have two options, the way I see it:
Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)
Create a separate job with the step's logic and then only use the new job that does the work
7
Use script instead to check status of currently running job.
SELECT job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed,
case when activity.last_executed_step_id is null
then 'Step 1 executing'
else '...
7
Create a job that is scheduled to start every minute. Have the job do something like:
WHILE 1=1
BEGIN
EXEC dbo.SomeProcedure; /* this would be the
name of a stored procedure that does the
actual work */
WAITFOR DELAY '00:00:01.000';
END
Thanks to Max Vernon, who gave me this answer earlier.
7
PostgreSQL doesn't have the concept of scheduled jobs.
You can use
cron or the like on Unix.
Windows task scheduler on Microsoft operating systems.
With these, just use psql to connect the db and feed it a sql file.
psql -d myDb -f "mySQL.sql"
If you want an EXTENSION that does some of this, you can check out pg_cron.
7
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s ...
6
I used this code from this answer to the Server Fault question Renamed a SQL Server, however maintenance plans still have old name:
SELECT x.*,
LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
SELECT id, ...
6
From stackoverflow (mafafu )
WAITFOR DELAY '00:00:02';
while exists (select * from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
where name = 'Job 1'
and stop_execution_date is null and start_execution_date is not null)
begin
PRINT 'Waiting...'
WAITFOR DELAY '00:00:...
6
I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query
USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO
The setting takes effect immediately without restarting the server.
Please refer Configure ...
6
You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ). I'd also specify INTERVAL=1 explicitly.
BEGIN
dbms_scheduler.create_job (
job_name => 'emp_test_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'emp_test;',
start_date => SYSTIMESTAMP,
enabled => true,
...
Only top voted, non community-wiki answers of a minimum length are eligible