Job History is not appearing

Question

Hi Team,

Job History was not appearing, can you please guide me.

Tx

---

subu

Answer 1

Would you please check-out below thread

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/353105a9-93da-4bc8-89ca-1c5d779641df

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 2

Hi Team,

as per your reply..in sql server agent --> Right Click -->Report --> Job Execution History

here the heading showing last 7 days but we are not able to see at least last two days also can you please give me any script is there to see last two days job history..

Tx

---

subu

Answer 3

Hello Subu,

Indeed the "Report" shows only the activity of the last 7 days.

Do a right-mouse click on the node "Jobs" => "View History" and you will see all activity of all jobs.

Optional you can do this on a dedicated job to see it's history.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Olaf

Yes you are correct i have to see all  the jobs activity..

I want to see last two day jobs history.. Can you please suggest me.

Tx

---

subu

Answer 5

Hello subu,

In the log viewer symbol bar there is a "Filter" option, here you can define a start/enddate.

Optional you can query it this way:

SELECT *
FROM msdb.dbo.sysjobhistory
WHERE run_date >= CONVERT(int, CONVERT(varchar(10), DATEADD(day, -2, GetDate()), 112))
ORDER BY instance_id DESC

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

Olaf,

Really thanks for your reply..Now we are correct, but job name was not displaying, even i have to use below query also

select * from msdb.dbo.sysjobhistory. we are not displaying job name can you pelase suggest me with job name also, its very issues to find out.

---

subu

Answer 7

Hello subu,

The join sysjobs table to the query to get the job name:

SELECT JOB.name
       ,JH.*
FROM msdb.dbo.sysjobhistory AS JH
     INNER JOIN
     msdb.dbo.sysjobs AS JOB
         ON JOB.job_id = JH.job_id
WHERE run_date >= CONVERT(int, CONVERT(varchar(10), DATEADD(day, -2, GetDate()), 112))
ORDER BY instance_id DESC

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 8

Hi Olaf,

I have tried the same script date was displaying only today's 20120726

DATEADD(day, -1, GetDate()), 112))

DATEADD(day, -2, GetDate()), 112))
DATEADD(<span class="pln

---

subu

Answer 9

And the problem is ... ? That you get also yesterdays activity? It's because you use the dateadd(..-1) function, this returns yesterdays date

SELECT JOB.name
       ,JH.*
FROM msdb.dbo.sysjobhistory AS JH
     INNER JOIN
     msdb.dbo.sysjobs AS JOB
         ON JOB.job_id = JH.job_id
WHERE run_date = CONVERT(int, CONVERT(varchar(10), GetDate(), 112))
ORDER BY instance_id DESC

You work as a DBA?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 10

Hi Olef,

Sorry for my English was bit bad. Using this the below script we will get yesterday report right,but im not getting.

i m getting todays report only ..  Convert(varchar(10),Dateadd(day, -2,Getdate()),112)

can you please suggest i want twodays back job history.

Tx

---

subu

Answer 11

For me this script works fine; are you sure that activities of today / yesterdas exists? Query the history table without a where clause.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 12

Olaf,

For you its work you are correct but.

in our job history was showing only one history only how can i enable more than one day history. pleas suggest me.

tx

---

subu

Answer 13

Hello subu,

In SSMS please check the SQL Server-Agent History settings, see SQL Server Agent Properties (History Page). For this do a right-mouse click on "SQL Server-Agent" => Properties and the goto page "History".

Maybe some activated the "Remove agent history" settings with "Older than" = 1 day, so that the history is deleted automatically after 1 day. 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 14

Olaf,

Remove aget history Older Than 4 weeks we have, but it is disabled, i have  tried put mark on that box it is but it is not Enable what should i do could you please suggest us.

---

subu

Answer 15

Hi Team,

My proublem was not cleared here

How can i enable jobs history, because some jobs history is not appearing and some jobs only one history is appearing

Olaf as per your suggestions everything  i will follow but still history is not apperaring. Can you please suggest me.

Tx

---

subu

Answer 16

Hi Team,

My proublem was not cleared here

How can i enable jobs history, because some jobs history is not appearing and some jobs only one history is appearing

Olaf as per your suggestions everything  i will follow but still history is not apperaring. Can you please suggest me.

Tx

---

subu

Answer 17

Hi Subu

Please follow instructions in below link and then give me values that you have specified History tab of SQL Server Agent Properties.

http://msdn.microsoft.com/en-us/library/ms190956

Thanks, Vishal

Answer 18

Hi Team,

I Did the as per your suggestion only but in sql server agent subplan_1,subplan_2,Subplan_3 History was not appearing please suggest me what action we have to take please suggest me.

Some of the maintaince plan jobs are failing but even single history also we are not able to look in to it?

Please suggest us really appriciated those guys.

Tx

---

subu

Answer 19

Hello Subu,

To view maintenance plan history right click on the maintenance plan and click on View history

--Manish T