Maintenance Jobs on SQL Server 2005

Question

Hi,

I have set up a couple of Maintenance Jobs on my SQL server (2005, SP2) like Rebuild Indexes, Check DB Integrity etc.

When i click on Job History of these jobs i dont see any entries / History / Records in Log File Viewer.

However in Job Properties, i do see last executed time correctly.

Please advice!

Answer 1

Make sure the Max job history log size and max job history rows per job are set properly to retain logs for your reference purpose.
You can check this setting by RIGHT CLICK SQL SERVER AGENT --> HISTORY tab --> Under the limit job history size check box.

For more info , have a look at http://www.mssqltips.com/tip.asp?tip=1231

---

Thanks, Leks

Answer 2

Hi Lekss..

Thanks for your response.

I already have Limit size of History log checked.
Max job history log size: 1000
Max Job history rows per job: 100

But still i dont see any history for my maintenance jobs. However i do see the history of other jobs like replications....

Please advice.

Answer 3

Can you try to apply SP3 wrap for 2005 and check this behavior again (Apparently there were numerous bugs in maint plans till sql server 2005 sp2)

---

Thanks, Leks

Answer 4

Lekss...if that was the case, i think i should be able to see history for other jobs as well. isnt it??

Answer 5

Are you able to see job history for other jobs created through MAINT plans ?

---

Thanks, Leks

Answer 6

Initially, i am not able to see any history for Maintenance Jobs that i have setup.

However if i run the job manually - SQL Agent -> Jobs -> start Job (Maintenance job), the history is recorded.

So i would say, History is not recorded for Schedule run Maintenance jobs. This is very straange??? Can any one advice me please!

thanks.

Answer 7

Can you try this.

Run your maintenance job and check the job history immediately and lets see if its there. -- Since you are able to view the history when you ran manually, it seems the total row 1000 is the problem, try to increase it to some 5000

Can you run the query below to see the total rows in sysjobhistory table

SELECT COUNT(*) FROM msdb..sysjobhistory

 

Is there any job running in more frequent schedules?

Answer 8

I'm with you. I wouldn't be surprised if there's some other job wich is executed very frequently and the 1000 total entries is consumbed and pushes away all the entries for this maint job.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 9

Do your maintenance plans themselves have a step in there to clean up the history? If so, what is that set to?

---

Joie Andrew "Since 1982"

Answer 10

Thanks Vidhya...

I believe that was the case. I changed the Max job history log size to 100000 and i am able to see the history for my Maintenance jobs now.

Yes i do have replication environment ont the same server with replications job running every 10 minutes....!

Thanks!

Answer 11

HI Rubsay,

That's why in my first post i asked you to check this ?

Make sure the Max job history log size and max job history rows per job are set properly to retain logs for your reference purpose.

Anyway i am glad that ur prob is solved.

---

Thanks, Leks

Answer 12

Thanks Lekks...And as i had mentioned...my figures to you.

That time i didnot know how those figures could affect what i am cing....! Now i had changed it to 100000 records...!!!

Many thanks for your asssitance always..!