SQL server 2008 r2 jobs history
-
Question: why 1 out of 2 scheduled tasks gets the history info but not the other. What are the requirements for jobs' history to get written out to the corresponding sys objects history tables?
server : 2008 R2 OS and SQL server .. all updates in place
FYI: if I run an SSIS package then the history is being written out, by the way.
any assistance would be appreciated. :w00t:
Cheers,
John Esraelo -
What are the jobs, and are you saying you get no history as in no record of execution? Or no details?
It would be helpful to provide more info on what's happening.
-
Are you referring to MSDB.dbo.sysjobhistory?
How often are the jobs scheduled to run?
If you go into the properties for the SQL Server Agent, under the History page what are the values for Maximum job history log size?
Have you tried checking the history immediately after the job has run?
-
no entries in history table
the jobs are simple: getting record(s) from a source and writing to another table.
There is no history record(s) for successful nor failure
I may be able to grab more info and share on this
be back
Cheers,
John Esraelo -
more info...
stopped them from running as scheduled.
run them 2 jobs manually (see attached) one of them has written out to the history table and one did not .. of course we cannot see the other one 🙂
the one that writing to history is an SSIS package.
I have checked to see if there is any process that truncates the history table and of course as you see the table is not truncated
Cheers,
John Esraelo -
What are the values in SQL Server Agent Properties > History for
Max job history log size
and
Max Job history rows per job.
-
see attached
I thought this setting were universal / global for all jobs... as seen in previous message, 1 works and the other does not ..
Cheers,
John Esraelo -
They are both set to 1000. That means if the job 1 is writing 1000 rows to the job history then you won't see any records for job 2. How many rows does the job you are seeing the history for write?
-
there are only 3 record and the table is no where near full
see previous snap-shots from history results.
see attached for more interesting info on the "agent" side.
beginning to believe that there may be a serious bug in the installation..
Cheers,
John Esraelo -
If you delete the job and recreate it does it record the history?
Are you sure no one is running dbo.sp_purge_jobhistory to clear the logs?
-
if you right click on SQL server Agent, and choose properties, what are the values you see here?
for me, with more than 60 jobs, some of them log backups ever fifteen minute,s the defaults are woefully small; it would take just a few "chatty" jobs to push the history past 1000 entries, so you'll lose sight of what has been performed.
i have mine at 10 to 50x the defaults, depending on the server.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That's very good question and yes I have dropped and recreated the non-conforming job.
Also, yes, I have checked and made sure that no other process is deleting, 1 because this is my test box, and 2 the table was not truncated as there are 3 records in there.
Cheers,
John Esraelo -
The only thing left for me to believe is the possibility of a bug(s) in the system.
It is not making sense why the history is written out when SSIS package is run but not TSQL statement in scheduled / non-scheduled task / job.
And, I have performed all the possible checks and inspections as what I have known and the ones shared here in the forum.
Appreciate all for taking a look at the situation.
thx again
:cool::-)
Cheers,
John Esraelo
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply