Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: 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!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I have never seen this.

    perhaps you dev was trying to clean up after himself?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    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!

    ......
    and coincidently bump my post
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo!

    ......
    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
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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 )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    Thanks for responding
    You're welcome; I didn't see it the first time around or I would have said something.

    Quote Originally Posted by pootle flump
    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.

    Quote Originally Posted by pootle flump
    Whoop whooop (or whatever you Yanks say )
    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?"



    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    By the way poots, I wiki'd Pootle Flump and came across the following entry:

    The Flumps

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

    [shudder /]



    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hmscott
    By the way poots, I wiki'd Pootle Flump and came across the following entry:

    The Flumps

    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....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •