Query/Report for Owner of all SQL Jobs

Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?
If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?Usual caveats re querying system tables (run at own risk etc):
SELECT J.name AS JobName
, L.name AS JobOwner
FROM msdb.dbo.sysjobs_view J
INNER JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
HTH|||Thank you very much! I'm just a newbie DBA admin :)

Query with OR never completes

This runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
and this runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
So why does this check ok but never complete when I run it:
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
'11/4/2005')
OR J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND schedtdate
BETWEEN '1/1/2005' AND '11/4/2005')
Same exact where clauses OR'd
JobId is Identity and Primary
Bob Confused and StupidLook at the estimated execution plan. That should tell you what you need
to know.
one alternate way to do it, if the OR won't optimize is to UNION the two
queries together.
rvgrahamsevatenein@.sbcglobal.net wrote:

>This runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>and this runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>So why does this check ok but never complete when I run it:
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
>'11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
>Same exact where clauses OR'd
>JobId is Identity and Primary
>Bob Confused and Stupid
>
>|||SQL gets compiled into an execution plan (how the processor navigates
through tables and indexes), before it is run, and even seemingly
insignificant changes in the SQL can result in an entirely different plan.
Using the Show Execution Plan feature of Query Analyzer, see how the plan is
changed when you instroduce the OR condition. If table scans are being
performed, then you may need to implement a new index.
Graphically Displaying the Execution Plan Using SQL Query Analyzer
http://msdn.microsoft.com/library/d... />
1_5pde.asp
Tips on Optimizing SQL Server Indexes
http://www.sql-server-performance.c...ing_indexes.asp
<rvgrahamsevatenein@.sbcglobal.net> wrote in message
news:1131126018.213438.13380@.g43g2000cwa.googlegroups.com...
> This runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> and this runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> So why does this check ok but never complete when I run it:
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
> '11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
> Same exact where clauses OR'd
> JobId is Identity and Primary
> Bob Confused and Stupid
>|||I changed a couple of things and execution came down from 55 seconds (I
thought it was never completing, but it was) to about 5 seconds.
Changing the "Between" on the dates to ">=...and <+" seemed to result
in a completely different execution plan. Strange since in the
"Between" version Sql was using ">=...and <+" anyway!
Bob Graham|||My co-worker here had the same problem yesterday in ORACLE.
She had a query with a few ORs and NOT INs.
After running for about 7 minutes she cancelled the query and called me
over.
I suggested changing the NOT INs to NOT EXISTs but still the same problem.
The next suggestion was separate the query in 3 and UNION them.
It ran in under 2 seconds.
Go figure, Microsoft and Oracle agree on something...
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:uOy8QhW4FHA.2888@.tk2msftngp13.phx.gbl...
> Look at the estimated execution plan. That should tell you what you need
> to know.
> one alternate way to do it, if the OR won't optimize is to UNION the two
> queries together.
> rvgrahamsevatenein@.sbcglobal.net wrote:
>|||Hmmm, Union worked well. Wish I had time to learn more about what was
wrong with the OR version, but must press on... Thank You!|||I've seen a lot of things like that. Looks like the optimizer likes to
go for a table scan when it encounters an OR.
Yet it seems to be more willing to find a good plan for every branch of
a UNION...

query to view current executing jobs

... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>

Query to rerun missed jobs after a backup and restore.

Does anyone know of a Query I can run after restoring a db that will go
though and rerun all the jobs that were missed between the time of the
final differential pulled and the time the differential was restored. I
have about 50 jobs and it's really annoying to go through each job
and run it manually.
SQL 2000 SP4
Thanks BTWWell If no one knows of a procedure, then I will have to create one,
but I will need help with the logic.
Right now it looks like all the information I require is stored in two
tables sp_help_job & sp_help_jobschedule this can be joined on the
job_id and will provide the last run date and time as well as the
intervals.
What I Am looking at is to rerun all jobs that occurred in the past,
that need to be run again because they were missed during time of the
last differential and the final restore.
The Logic:
I know the current time, and I know when the next job is to be run. So
what I need to do is first get the current time, and compare it to the
last executed time. If the job interval is 15 minutes or less, don't
run the job. If the job time is greater the specified duration, run the
job.
I hope that makes sense.
-Matt-|||Let me see if I understand your probem. Are you asking what jobs would have
executed in a certain time window in the past, assuming all the schedules
have stayed the same? I suppose you can write some logic that would
duplicate our schedule calculation, but it's probably worth trying to
understand better how come those jobs have been missed. Did you shut down
agent during the time window when you did backup/restore?
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew" <MKruer@.gmail.com> wrote in message
news:1144951366.394182.197090@.i40g2000cwc.googlegroups.com...
> Well If no one knows of a procedure, then I will have to create one,
> but I will need help with the logic.
> Right now it looks like all the information I require is stored in two
> tables sp_help_job & sp_help_jobschedule this can be joined on the
> job_id and will provide the last run date and time as well as the
> intervals.
> What I Am looking at is to rerun all jobs that occurred in the past,
> that need to be run again because they were missed during time of the
> last differential and the final restore.
> The Logic:
> I know the current time, and I know when the next job is to be run. So
> what I need to do is first get the current time, and compare it to the
> last executed time. If the job interval is 15 minutes or less, don't
> run the job. If the job time is greater the specified duration, run the
> job.
> I hope that makes sense.
> -Matt-
>|||Thanks for the reply.
The reason why the jobs were missed is simple. The databases in
question are being replicated in a live environment. So we are making a
backup of the database, restore that copy to a different system, and
then do a differential on the database. The problem lays in the doing
the restore for the differential. During a differential restore there
is a period of time (depending on the size) that jobs can run and not
be on the diff, hence there was a change made on one database and not
the other one. This is where the job would need to be run. These
databases are running 24/7 with tens to hundreds of gigs of data, and I
can literally say that delaying a diff for more then 2 days, its faster
to pull a new full, there is just that much data being moved/updated.
Somewhat off topic. I really hate the way the DB stores the date and
time. This would be so much easier if the date/time was a single
integer.|||BTW I think I have all the information I need using this Join
Statement.

code:
SELECT dbo.sysjobs.job_id, dbo.sysjobs.name, dbo.sysjobs.enabled,
dbo.sysjobservers.last_run_outcome,
dbo.sysjobservers.last_outcome_message,
dbo.sysjobservers.last_run_date,
dbo.sysjobservers.last_run_time, dbo.sysjobservers.last_run_duration,
dbo.sysjobschedules.next_run_date,
dbo.sysjobschedules.next_run_time,
dbo.sysjobschedules.freq_recurrence_factor,
dbo.sysjobschedules.freq_type,
dbo.sysjobschedules.freq_interval,
dbo.sysjobschedules.freq_subday_type,
dbo.sysjobschedules.freq_subday_interval
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id =
dbo.sysjobschedules.job_id INNER JOIN
dbo.sysjobservers ON dbo.sysjobschedules.job_id =
dbo.sysjobservers.job_id
Where last_run_outcome != '1' and last_run_outcome != '5' and
dbo.sysjobs.enabled = '1'
Order by dbo.sysjobs.name

query to list failed jobs only

Can i get a query to list only failed SQL Agent jobs ?msdb.dbo.sp_help_job will return info about the SQL Agent jobs. Failed
jobs are represented by (last_run_outcome = 0). Unfortunately,
sp_help_job calls another proc that does an INSERT...EXEC... so you
can't wrap the results of the "exec msdb.dbo.sp_help_job" into a little
batch that captures the results in a temp table and selects only the
rows from that temp table where last_run_outcome = 0. But you could do
it manually by running sp_help_job in QA, saving the results to a text
file (or CSV), opening the file in Excel and sorting by that
last_run_outcome column to get the failed jobs at the top of the list.
I know it's a little ugly but it would work.
Alternately, if you don't care about multi-server jobs (just local jobs)
then you can get the info you're after (more or less) just by joining
the msdb.dbo.sysjobs table and the msdb.dbo.sysjobservers table. Like this:
select j.job_id, j.[name] from dbo.sysjobs as j
inner join dbo.sysjobservers as s on s.job_id = j.job_id
where s.last_run_outcome = 0
order by j.[name]
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>Can i get a query to list only failed SQL Agent jobs ?
>
>|||Here's a query to find jobs with any failed steps:
SELECT DISTINCT j.name
FROM msdb.dbo.sysjobhistory
inner join msdb.dbo.sysjobs
on h.job_id = j.job_id
where h.run_status <> 1
and h.step_id > 0
If you want to limit the scope to the past w's history, add:
and convert(smalldatetime, convert(char(8), h.run_date), 112) > getdate() -
7
http://www.aspfaq.com/
(Reverse address to reply.)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uhSZDAhGFHA.904@.tk2msftngp13.phx.gbl...
> Can i get a query to list only failed SQL Agent jobs ?
>

query to get list of scheduled jobs/duration

Does anyone have a query that will list all the jobs, their schedule and
duration?
Many thanks in advance!
Robin,
The attached stored procedure lists job history, status, etc. for a given
date.
-- Bill
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:972F67C8-62D5-4664-9006-28ED8C2E53CF@.microsoft.com...
> Does anyone have a query that will list all the jobs, their schedule and
> duration?
> Many thanks in advance!
|||Thank you for your reply!
I did not get the SP though - would yuo please repost it?
many, many thanks!
"AlterEgo" wrote:

> Robin,
> The attached stored procedure lists job history, status, etc. for a given
> date.
> -- Bill
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:972F67C8-62D5-4664-9006-28ED8C2E53CF@.microsoft.com...
>
>

query to get list of scheduled jobs/duration

Does anyone have a query that will list all the jobs, their schedule and
duration?
Many thanks in advance!underprocessable|||Thank you for your reply!
I did not get the SP though - would yuo please repost it?
many, many thanks!
"AlterEgo" wrote:

> Robin,
> The attached stored procedure lists job history, status, etc. for a given
> date.
> -- Bill
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:972F67C8-62D5-4664-9006-28ED8C2E53CF@.microsoft.com...
>
>

Query to find who runs jobs

I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle

Not quite. You should check msdb for all things SQL Agent/job related.

Rather than querying the tables directly though, i'd recommend using the system sprocs:

Check out sp_update_job, sp_add_job, sp_help_job (plus associated sprocs) in Books Online.

HTH!

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?
> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/...88/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/Article/ArticleID/42588/42588.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query Time Outs

Hi All.

I have some rather large SQL Server 2000 databases (around 60GB).

I have set up jobs to re-index the tables and update statistics every
sunday. This worked will for a few months. Now after a day or two of
using it the connections to it keep timing out. If i start the jobs
manually, all is well for two days or so.

Surely there can be a better solution to this ?

TIA.
Ryan,.Are the timeouts happening on the tables or on the queries run against
the tables ? The tables shouldn't time out. The queries could well
time out. Try running your queries with the execution plan showing. It
may give you some indication of what is causing the delays.

Also, do you get the same results on each PC and the server ? Try
running the query on the server if possible to see if it is server
based or networking. Networking issues may be causing a bottleneck
which then slows you down. Worth looking at if possible.

The trick will be narrowing this down to where the problem actually
lies, not just where it shows up.

budgie@.doormat.za.org (Ryan Budge) wrote in message news:<8b601867.0311200322.281b643d@.posting.google.com>...
> Hi All.
> I have some rather large SQL Server 2000 databases (around 60GB).
> I have set up jobs to re-index the tables and update statistics every
> sunday. This worked will for a few months. Now after a day or two of
> using it the connections to it keep timing out. If i start the jobs
> manually, all is well for two days or so.
> Surely there can be a better solution to this ?
> TIA.
> Ryan,.|||Hi.

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0311200631.7f7fdf6a@.posting.google.com>...
> Are the timeouts happening on the tables or on the queries run against
> the tables ? The tables shouldn't time out. The queries could well
> time out. Try running your queries with the execution plan showing. It
> may give you some indication of what is causing the delays.

OK. Will do.

> Also, do you get the same results on each PC and the server ? Try
> running the query on the server if possible to see if it is server
> based or networking. Networking issues may be causing a bottleneck
> which then slows you down. Worth looking at if possible.

I have tested the application on the server and on client PCs. It
does not seem to make a difference. Timeouts and slow performance is
on both...

> The trick will be narrowing this down to where the problem actually
> lies, not just where it shows up.

Yep... Im sure. :-).

I remeber googling around or reading some of the books online and
reading about the sample percentage when updating statics and indexes.
Is it possible to re-index a table with a greater percentage to
enable the job to only run once a week ?

What kinda percentage is safe to use ?

Thanks for the suggestions.
Ryan.

Query Table Without Data

I'm writting a stored proc that has to query 2 tables. One table is a table of "jobs" and the other table contains jobs that have been invoiced (2 tables are jobs and invoicedJobs). The invoiced table only contains records for jobs that have an invoice and not jobs that do not have an invoice.

My dilemma is that I need to write a query that can retrieve allun-invoiced jobs in my stored proc. You can't rightly join a table that does not have a relationship with another table (can you?). So in my query for jobs with an invoice, I simply join my jobs table and invoice table based on a job id that both tables contain. But how could I perform a query for jobs thatdo not exist in my invoice table inside my stored proc? Any help would be greatly appreciated.

SELECT jobs.*
FROM jobs
LEFT JOIN invoices ON (jobs.id=invoiced.id)
WHERE invoiced.id IS NULL

or

SELECT *
FROM JOBS
WHERE id NOT IN (SELECT id FROM invoices)

|||

Awsome. Thank you.