30
Even if you were running the soon-to-be-released (at time of writing) PostgreSQL 10 or the current PostgreSQL 9.6 not an ancient release like 8.3, there's still no built-in task scheduler.
Something like PgAgent or external cron jobs is required, there is no convenient workaround.
The background workers feature introduced in 9.3 should hopefully permit a ...
21
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 ...
17
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, ...
14
Option 1
In Job2, create a job step of type TSQL Command. In the contents, have it run the existing job (sp_start_job)
EXECUTE msdb.dbo.sp_start_job 'Job1'
That is going to run asynchronously, thus after it starts calls the stored procedure, it will return and perform the next step in the job. It will not wait for that started job to complete. If the ...
14
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
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
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 ...
10
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 ...
10
Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job.
Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.
9
Discontinued Database Engine Functionality in SQL Server 2016 indicates
ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.
8
Every 20 minutes (this seems to NOT be configurable), the 'next run' information in sysjobschedules is updated by looking at the information you can see in sysschedules.
You know how when you define a job you have to go through a slightly convoluted process of rather than just saying 'run this job every monday', instead defining a schedule that says that, ...
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
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).
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
I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction ...
7
Right click on the job whose steps you want to add and choose "Script Job As->Create to new query window", in the resulting script look for all sections that have this format
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'<stepname>',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0, ...
7
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 ...
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
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_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
SELECT
j.name,
rd = AVG(DATEDIFF(...
7
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)]
7
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 ...
7
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 ...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
