Hot answers tagged

41 votes

How to run recurring tasks on Postgresql without an external cron-like tool?

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',...
user avatar
20 votes
Accepted

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

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 ...
user avatar
  • 13.8k
13 votes
Accepted

How to schedule jobs in Sql server 2008 for less than 10 sec?

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 ...
user avatar
  • 67.5k
12 votes

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

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 ...
user avatar
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
  • 34.2k
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
11 votes

Does @os_run_priority in sp_add_jobstep actually work in SQL Server 2008 R2?

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 ...
user avatar
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,995
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

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

How do you get an average of job history times?

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, ...
user avatar
8 votes

Save multiple SQL jobs in single files

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
user avatar
  • 3,953
8 votes

What is meant by a blind write in a schedule?

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(...
user avatar
  • 181
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

How to create jobs in PostgreSQL?

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 ...
user avatar
  • 56.7k
8 votes
Accepted

How do I find a job that sends a specific email?

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 ...
user avatar
  • 3,239
7 votes
Accepted

Job showing the running sign but not currently running

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, ...
user avatar
  • 3,953
7 votes
Accepted

Alert when Job isn't triggered

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 ...
user avatar
7 votes

Disabling certain steps in a job

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 ...
user avatar
7 votes
Accepted

How can I run a SQL job every 1 second?

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 ...
user avatar
7 votes
Accepted

How to call a stored procedure in an Oracle Scheduler job

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 =>...
user avatar
  • 8,721
7 votes
Accepted

Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)

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 ...
user avatar
  • 7,783
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: ...
user avatar
  • 4,589
6 votes

Calling a SQL Server job within another job

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 ...
user avatar
6 votes

How to changes or update local server connection in Maintenance plan job

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('...
user avatar
  • 183
6 votes

SQL Server: Frequent Login timeout expired in Linked server

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; ...
user avatar
  • 765
6 votes
Accepted

Get Job step run for all jobs and job steps using TSQL

This should get you close, if NULL for ProxyName then it's using the SQL Server Agent credentials. USE msdb SELECT J.job_id , J.name , S.step_name , S.step_id , P.name AS ...
user avatar
6 votes
Accepted

Can an SQL Job Deletes itself after its end date?

Can an SQL Job Deletes itself after its end date? I have created a job which will sets up its start date and end date automatically with a dynamic value. Is there a way to code it while ...
user avatar
6 votes

How to create jobs in PostgreSQL?

You can configure your recurring tasks by creating database jobs in PostgreSQL. PostgreSQL provides one external tool to configure PostgreSQL jobs, its name is PGAgent. PGAgent (a part of pgAdminIII) ...
user avatar

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