Job Execution Information not written to system tables

SS 2005 64Bit SP2

Hello Chaps

Intermittent problem with the SQL Agent job history not getting written to the history table. Background:
Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs.

There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it.

Stuff run to try to track the job:
EXEC sp_help_jobactivity @.job_name = 'MyJob'

EXEC sp_help_jobhistory @.job_name = 'MyJob'

SELECT *
FROM dbo.sysjobhistory
WHERE job_id = 'MyJob GUID'

The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null).

sp_help_jobhistory had some historical records but nothing since mid last month.

sysjobhistory correlated with sp_help_jobhistory as you would expect.

Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June).

We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.
Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it.

We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will.

Ta!I have never seen this.

perhaps you dev was trying to clean up after himself? ;)|||I have never seen this.

perhaps you dev was trying to clean up after himself? ;)I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p

.....
and coincidently bump my post ;)|||I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p

.....
and coincidently bump my post ;)

What are your job history retention settings? What's the maximum number of rows of history total and per job?

By default, I believe that the settings for job history retention are 1000 rows total and 100 rows per job. It's one of the first things I change when setting up a new server. My "default" settings are 10,000 rows total and 1,000 rows per job. Sometimes I feel that even this is not enough.

Also, are there other jobs (such as replication) that run with a great deal of frequency? Like once per minute or so? This would have the tendency of "hogging" the history table.

Ta (or whatever it is you Brits say),

hmscott|||Thanks for responding :)

Yeah - our settings were at the default. However we don't run many jobs (no replication, no "sniffers" running every minute). I checked the number of records in the tables when we had the problem and neither the total nor per job numbers had quite hit the limit. I did up them just to be sure. AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.

Whoop whooop (or whatever you Yanks say :D)|||Thanks for responding :)

You're welcome; I didn't see it the first time around or I would have said something.


AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.

You are correct. I have, however, seen some situations where the history gets overwritten rather quickly. I wasn't certain how long after job execution you were looking in the history tables.

The only other thing I can suggest is to run a trace on a job that completes normally (and writes history to the history tables) and then run another trace on the job with which you are having issues.

Whoop whooop (or whatever you Yanks say :D)

No, no, you obviously have no understanding of us d@.mn Yankees. We say things like, "Reach for the sky, partner!" or "This here forum ain't big enough for the two of us" or, sometimes, "Make mine a double latte soy skinny harmless, and my friend Serge here will have a half-caf grande with legs. Oooo, is that diamond in your ear real?"

:D

Regards,

hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:

The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:

Now I am afraid I will be having nightmares for the next month.

[shudder /]

:D

hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:

The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:

Now I am afraid I will be having nightmares for the next month.

[shudder /]To help your nightmares along, I am the one in the white hat :)

Ta re trace. It is tricky catching it from the beginning since it is intermittent. We did run profiler whilst it was running this time but there seemed to be nothing unusual going on...