Questions tagged [jobs]
A scheduled task managed by the database, for example using SQL Server Agent, Oracle dbms_job, or MySQL Event Scheduler.
0
votes
0answers
22 views
Ola Hallengren - Parallel Backup Jobs failing on SINGLE_USER DB - 'Database is in transition' [952]
I'm using Ola's DatabasesInParallel option for "multi-threaded" execution. Last night a db was temporarily placed into SINGLE_USER for an application-based archive process and it caused all 5 threads ...
4
votes
1answer
47 views
What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?
I'm reading up on the SysSchedules table in the Microsoft API, and am trying to incorporate using this table in a query.
In the documentation, the Freq_Interval column seems to be defined out for ...
4
votes
1answer
255 views
Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)
Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update.
The reason was quickly found: an update statistics job step (T-SQL) ...
3
votes
5answers
83 views
How to stop a job for the rest of the day after the relevant condition has been satisfied once?
I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything.
My problem is that once the IF condition is true, it ...
1
vote
0answers
50 views
Giving Access to only one SQL Agent Job
I will only need to allow a user to execute a specific sql agent job rather than be able to execute the other jobs that are there . Please advice how to accomplish this
0
votes
0answers
50 views
how to collect the information provided by tokens in order to troubleshoot failing jobs quickly?
I was working on a way to take advantage of the SQL Server Agent Tokens and Macros because I work a lot with jobs and job steps and sometimes I have to notify different people depending on the outcome ...
0
votes
1answer
55 views
Rebuild index error: “The job was stopped prior to completion by User”
I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 ...
3
votes
2answers
65 views
function to check whether the current server is involved in an availability group and if so, whether it is the primary
I used to have 2 servers on an availability group, but there was a problem with one of them servers involved,while patching, and to make a long story short I removed all the databases from the ...
0
votes
1answer
86 views
How to create SQL Agent job to automatically restore a database from latest backups?
There are 3 different folders which store respectively full, differential, and log backups. Different types of backups occur in different schedules. Now I need to create a new job which will ...
2
votes
2answers
82 views
availability groups - checking for primary server - cannot call a procedure with parameters
In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group.
So the ...
0
votes
1answer
39 views
How to format the output of this query into year…second?
I have this query that given @secs a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.
take for example:
declare @secs int
select @secs = 19475298
...
1
vote
0answers
382 views
dbcc failing with Cannot continue the execution because… alwayson having a change of state event for each db
I got a job DatabaseIntegrityCheck - USER_DATABASES, failing with the following error:
Date and time: 2018-10-29 02:34:13 Command: DBCC CHECKDB ([one of my
databases]) WITH NO_INFOMSGS, ...
1
vote
0answers
69 views
OLA Output File Cleanup - Better Options
Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
0
votes
1answer
80 views
Why do we need to use proxies for syadmin job steps?
I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin.
I just don't get why we need to use proxies even the ...
0
votes
1answer
80 views
Log shipping - Job schedules
When we configure the Log Shipping, there are jobs automatically created that run on schedule. What happens if db size grows or there is network latency, does SQL Server auto handle the job schedules? ...
2
votes
1answer
31 views
Schedule database backup question
Good day. This may be a stupid question and you can give me a stupid answer if you need to. I know you can schedule a task to backup a sql database at logon, shutdown, specific time, etc. But What I ...
0
votes
1answer
55 views
ORA-24155 - Why am I getting this error when creating a chain rule
When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense.
I stop and drop all jobs/chains and then ...
0
votes
1answer
136 views
CmdExec Powershell Job Step Escape Double Quotes
I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with ...
1
vote
2answers
163 views
How to stop the SQL Server Agent Job Scheduler with T-SQL?
I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)
There are several jobs scheduled to ...
0
votes
1answer
47 views
SQL job ignoring IF condition
I have a job with a few steps(that run reports information) and i added a query i wrote to the last step.
Sometimes the job fails because of factors i can not control (application issues)
and my query ...
3
votes
1answer
450 views
INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'
I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache.
As part of step 1 in the SQL job,i filter only the serial plans and insert it into a ...
1
vote
2answers
602 views
How to find the list of SQL Server jobs using PowerShell?
I need to create Windows PowerShell Scripts that will check and list down all SQL Jobs scheduled on a given database server.
Is it possible to find out the list using PowerShell Scripts? If yes, how ...
1
vote
4answers
86 views
replace all references to a drive inside the jobs to another drive
I have a server where all my sql server backups are currently being saved to the G: drive.
Now there is no enough disk space on drive G: to keep backups for the last 5 days.
I have asked to add ...
0
votes
1answer
248 views
length of the result exceeds the > length limit (2GB) of the target large type
Getting the following error message on the Sql Server Error Log:
SQLServer Error: 599, WRITE: The length of the result exceeds the
length limit (2GB) of the target large type. [SQLSTATE 42000]
(...
0
votes
1answer
48 views
job send email to developers upon complexion and include DBAs if it fails
I have a job that sends an email to an operator (which contains a list of emails within it) upon complexion.
Some of these people that are on that list are the developers who build everything in that ...
0
votes
1answer
82 views
Even out disk i/o
We noticed that when a few of our Microsoft SQL server jobs run disk I/O goes from a background of 1-5 disk queue length to 100-700 disk queue length. The thing is the jobs only run for 1-2 minutes so ...
2
votes
1answer
906 views
How do I find a job that sends a specific email?
I had a job sending an irrelevant email to a group of users.
The title of the email was Staging -> [AUPAIR] arrivalDate trigger issue.
When I query the jobs table in the msdb database using the ...
0
votes
1answer
38 views
What's the best way to pass a variable/valuable between steps in a SQL Server job
For example, if I create a dtsx package that fills out an excel sheet, but only email it out if there is actual data?
0
votes
0answers
47 views
Is there a date modified timestamp for steps in a job on msdb?
I need to figure out which steps have been modified since the beginning of the month. I can find which jobs have been modified, but some of the jobs have 20+ steps, so knowing which specific step was ...
1
vote
1answer
50 views
Scheduled tasks in Oracle 11g
I have a scheduled task in Oracle 11g scheduled to run at 12:00 and 17:00 each day. If the first run takes more than five hours, what would happen to the second scheduled run? Would it simply be ...
0
votes
0answers
22 views
Agent Job Activity Time Last Run Not in Sync with Computer Clock
I am updating jobs in a database in preparation for log shipping. I ran a full back up and noticed in the Job Activity Monitor that the time last run is showing for the time zone I am in and not for ...
0
votes
1answer
58 views
Job Activity Monitor - Log File Viewer is showing Duration for running jobs
As you can see in Log file Viewer it has a column Duration which change for a running job.
I want to include this column in my query (from where I get jobname etc)
Any idea to make a SIMPLE script? ...
-1
votes
1answer
45 views
Job duration email
I have need to be able to generate two types of alerts:
We have a sql job which runs every 1 hour. I need an alert to let us know the time it took to complete the job
As the count for Table1 ...
0
votes
0answers
97 views
SQL Job agent get running jobs, failed and canceld jobs in same query
I have 2 queries I want to combine into one, as below.
I want to build on these queries due to restrictions I have as a user against the server. I can run exec sp_help_job @execution_status (it gives ...
1
vote
2answers
1k views
How do I get a run duration in minutes for each individual step in a single Job?
The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.
0
votes
1answer
130 views
Job Agent sysjobhistory run_status=0 failure but which last step was a failure?
I am getting confusing results from sysJobHistory. The message can sometimes say last step was 2 but the row from instance_id says 0.
Below are some examples below when run_status=0.
Any idea how ...
1
vote
1answer
217 views
Multiple procedures to schedule jobs
I have a package named pkg_cd. In it there are 8 procedures:
pkg_cd.PRC_CD_CASEDATAENTRY;
pkg_cd.PRC_CD_DEDUP;
pkg_cd.PRC_CD_CIBIL;
pkg_cd.PRC_CD_TVR;
pkg_cd.PRC_CD_SANCTION;
pkg_cd.PRC_CD_DOWNPAY;
...
2
votes
2answers
762 views
View SQL Server Agent jobs -> MSDB roles
When attempting to view the jobs' properties in SSMS, the users would get the error execute was denied on the object sp_help_targetserver. This is a sys SP in MSDB.
The users have
...
1
vote
1answer
90 views
Measure Agent Job failure and running jobs with 'execution_status'
Considerations
I need to create a Dashboard with job failure AND running jobs.
I have a code to get run_status etc.
I am not interested in succeed jobs.
I extract failure from run_status = 0. ...
0
votes
0answers
156 views
Ola Hallengren's Backup solution error message after migrating to SQL 2012 using sqlcmd stops job, t
I am using Ola Hallengren's Maintenance script, and I am having an issue executing scripts. I'm getting the below error.
If I reboot SQL Server Agent Services and re-ran the jobs it works fine for a ...
1
vote
0answers
176 views
SSIS FTP Package job fails when scheduling through SQL Server Agent job
I am using WinSCP inside SSIS Execute task for downloading and moving the file in FTP location. It works great through visual studio. I am passing parameters inside the SQL Server Agent job and the ...
1
vote
1answer
277 views
Cannot run a job to update table on a linked server
I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) ...
0
votes
2answers
2k views
T-SQL: How create job that run monthly
I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL.
I know how to schedule a job daily at a specific time:
...
1
vote
4answers
283 views
SQL Server Agent Job - Adding Version Number
I would like to know how to add a version number to a SQL Server Agent job without using the description field, and with it being an attribute of the job.
A similar question was asked at ...
0
votes
0answers
192 views
Same Domain Linked Server Login Failing
I have several SQL Server instances. All of them are setup in the same domain, and have been working for years. One of these instances serves as an aggregator for the data of the others, and a sync ...
1
vote
1answer
46 views
Number of databases on a server. Should I try to consolidate these objects together? [closed]
Background
Hi, as a BI developer I create various scripts for a middle-size company (200-300 employees). These scripts are used to manipulate / aggregate / clean data from different sources and ...
0
votes
2answers
481 views
PLS-00306 dbms_scheduler.create_job
I'm not an expert about DBMS_SCHEDULER.CREATE_JOB and i've been asked to create a job. When i run it i get these errors:
ORA-06550: line2,column 2
PLS-00306: wrong number or types of arguments in ...
-1
votes
1answer
66 views
DST Back up plan
Back up schedule for log backup (using Ola's scripts) as follows:
Start Time - 2AM
End Time - 11.59.59PM
Interval - Every 15 minutes.
On November 5th, all the back ups went as detailed above. ...
-2
votes
1answer
104 views
reattach database jobs to another identical schema
I have a working DB holding schema1.
I needed to clone the schema1 to schema2. I did this with expdp and impdp. Everything worked fine, least when doing impdp for metadata I got the following error:
...
1
vote
1answer
320 views
Change logging level programmatically for multiple jobs
I have multiple Sql Agent jobs, with multiple steps each. For a large subset of these jobs, I want to set the logging levels to PERFORMANCE instead of BASIC -
the logs are taking a lot of log space, ...
