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 ...
Your friend is sqlcmd (Microsoft Technet)
Create a SQL file with the script required to run your cleanup job
Run the script with sqlcmd.exe and any required parameters
Create a Windows Scheduled Task and add the command with all the required parameters
sqlcmd -d YOUR_DB -E -i YOUR_SCRIPT.SQL -o OUTPUTFILE.TXT
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 ...
At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers.
While this is largely subjective (and it's going to be hard to derive a "correct" answer ...
You don't need Management Studio to execute queries.
If you really have no option to have somebody schedule a job for you, you should look into sqlcmd as noted in hot2use's answer
If you are on a version where that is not supported (since you didn't specify a version) there is also osql which is a command line client, but that tool has been deprecated.
Personally I think the biggest caveat would be the difficulty in keeping the list of jobs organized. As far as I'm aware you can't create folders to organize the jobs, so a large number would be cumbersome. I'm not 100% sure of this, though, since none of my servers has more than a dozen or so jobs. Server 2008 and later's Task Scheduler allows for much ...
If you are not comfortable using Powershell, then you can go for native service broker functionality.
This might sound complex, but below links has all the scripts :
Scheduling Jobs in SQL Server Express - Part 1 and
Scheduling Jobs in SQL Server Express - Part 2
Alternatively, you can use Standalone SQL Agent - still in BETA statge from codeplex.
You can ...
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 sp_help_jobschedule stored
Which is why your next_run_date & next_run_time columns could show out of date data.
For a much longer answer & deep ...
As per the code in your comment:
@profile_name = 'SQL Mail',
@recipients = 'firstname.lastname@example.org',
@subject = 'T-SQL Query Result',
@body = 'The result from SELECT is appended below.',
@query = '(include script from above)'
The reason this won't work is the same reason why this wouldn't work:
Events are not "running" unless they are actually in the process of executing.
What you see in your processlist is the actual event scheduler, not the event itself. You will always have this entry when the event scheduler is enabled.
mysql> show processlist;
With SQL Server Express, you could use powershell to execute a stored procedure automatically through Windows scheduler service.
There is a great article that shows how to use Powershell to EXEC stored procedures here:
You can also look at this question that deals ...
I recommend using Ola Hallengren's backup solution.
The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure.
sqlcmd -E -S ...
In simple terms you've run out of space in the primary filegroup of the Reporting database.
The data in a database is stored in FileGroups. The main filegroup (and the one created by default) is Primary. By default this is where everything is put unless you specify otherwise. Most databases are just going to have the Primary filegroup.
The error you ...
I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along ...
The nice part about MongoDB is that it is open source, so your answer is in the code, starting with comments about UnusedLockCleaner:
Periodically purges unused lock buckets. The first time the lock is
used again after cleanup it needs to be allocated, and similarly,
every first use by a client for an intent mode may need to create a
You can use this query directly to get all the names of jobs that include specific text (for example "MyText"
SELECT j.job_id, j.[name], s.command
FROM sysjobsteps s
right join dbo.sysjobs j on s.job_id = j.job_id
WHERE command LIKE '%MyText%'
** Execute query on multiple SQL Servers can be done using SSMS by creating a local server group or a Central ...
As it is using a cron-style schedule definition it is unlikely to support sub-minute granularity as most (all?) cron implementations don't.
A hack I've used to run a task more often than every minute with cron is a script that cron calls once per minute, which performs its task, sleeps a bit, then repeats. For example to run for times per minute: dotask; ...
If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with.
SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';
You can set a named time zone like:
You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up.
Streaming replication isn't really well suited to the purpose you describe. You can set it to have ...
For optimal store and later search, I would consider using two tables.
The first table would hold the event/schedule definition (frequency, internal, recurrences etc) - holding 1 line per event
The second table will be maintained by a process running on the first table and calculating->adding the data for future occurrences of each event.
Based on your ...
Not sure what you mean by "without any transaction". But normally timed scripts / tasks are executed as jobs, through the SQL Server Agent. See here for an example and explanation.
I just invented a lame example that might help.
Have you ever driven by a gas station while they are changing the prices? (And the prices are big numerals that need to be taken down and new numbers put up.)
There can't be two guys changing the same price at the same time -- exclusive lock.
Everyone riding by can read the numbers -- shared lock (sort of). ...
You will need to have the Full Recovery Model enabled on all four databases.
Now you will issue a full backup on all four databases, let's say ten minutes before the point in time you require.
Now you will do a transaction log backup of each database, at a point in time immediately after the exact moment you need.
On the other end, you will restore each ...
You need to search the command column in the msdb sysjobsteps table of each server.
SELECT sj.name FROM sysjobsteps js
join sysjobs sj on js.job_id = sj.job_id
WHERE js.command LIKE '%stored_procedure_name%'
I typically use a PowerShell script to iterate through all of my servers when I need to run something like this on ...
Attribute MAX_RUN_DURATION does not terminate the job, it only raises an event.
I created a procedure like this:
procedure SchedulerWatchdog(jobName in varchar2) is
cursor Jobs is
where JOB_NAME = jobName
AND STATE = 'RUNNING'
AND systimestamp - LAST_START_DATE > MAX_RUN_DURATION;
for aJob in ...
I found a patch to this problem. The idea is to create a bash file:
echo $1 > $2
And now, you can create and run your job:
To schedule jobs from the db server we'll need to enable trust authentication in pg_hba.conf for the user running the cron job.
We'll also need to either run UPDATE cron.job SET nodename = '' to make pg_cron connect via a local (unix domain) socket or add host all all 127.0.0.1/32 in pg_hba.conf to allow access to the pg_cron background worker via a local ...
Personally I would schule it using the Windows Scheduler. You might want to get robocopy if you are copying your backup files to another server. Having an email client that you can send emails from DOS can be helpful. But otherwise you would just write .BAT files and or a Powershell file. You want a process that creates a backup log and checks that log for ...