August 23, 2012 at 2:07 pm
Hi -
We migrated to SQL 2012 and now a few SQL Jobs don't seem to have job history (and I know they ran successfully).
The jobs exist (msdb) in sysjobs. When I try to query the data in sysjobhistory, the record (job_ID) is not present.
When I look at sysjobactivity, the records do exist.
Anyone ever experienced SQL jobs history not displaying when right clicking the job and selecting 'View Job history'?
August 23, 2012 at 2:24 pm
It might be a UI or server bug.
What is the build # of the server?
SELECT SERVERPROPERTY('ProductVersion');
What is the build # of your copy of SSMS?
Help Menu > About
What does this tell you?
USE msdb;
GO
EXEC dbo.sp_help_jobhistory
@job_name = N'Name of One of Your Jobs';
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 2:34 pm
cheshirefox (8/23/2012)
Hi -We migrated to SQL 2012 and now a few SQL Jobs don't seem to have job history (and I know they ran successfully).
...
Have you upgraded your Management Studio client (SSMS) to 2012 also? I ask because if the version of the SSMS you are using is lower than the version of SQL server that you are connecting to, you will see many strange things similar to this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 23, 2012 at 2:56 pm
Hi Guys -
Thanks for the responses...
Product version: 11.0.2325.0
Microsoft SQL Server Management Studio11.0.2100.60
For that Job name when I run sp_help_jobhistory, the result set is 0 records.
----------
Yes, I installed and running the SQL 2012 client tools.
Any other information is greatly appreciated.
August 23, 2012 at 6:03 pm
What are the job history retention settings? Right click SQL Server Agent/Properties - History.
The defaults are Maximum log size (in rows) 1000, Max history per job 100. If some jobs run very often (once a minute) they quickly begin forcing out the history on other jobs. Try increasing the Max Log Size, but watch out because there's a performance hit on MSDB if the log gets too big (200000 rows). Also work out the maximum rows you need per job, bused on your important job(s) and adjust this value. I work on "Max history per job" * "Number of Jobs" + 10% <= Max Log Size. This leave room for a 10% increase in the number of jobs before you will start losing any history.
Cheers
Leo
Nothing in SQL Server is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 24, 2012 at 7:26 am
Hi -
I updated the values yesterday.
I set Max job history log size (in rows) to 3000 and Max job history rows per job to 1000.
I will update the values again today to 4000 and 2000.
Thanks for providing feedback.
August 24, 2012 at 1:36 pm
And are you seeing history now?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 26, 2012 at 7:08 pm
cheshirefox (8/24/2012)
I set Max job history log size (in rows) to 3000 and Max job history rows per job to 1000.I will update the values again today to 4000 and 2000.
Thanks for providing feedback.
I wouldn't set the rows per job that high, the aim is to keep the ratio of Total history/per Job history correct, so you don't lose hisory in any job. 3000/1000 only allows 3 jobs to each reach a thousand, which is less than a days worht of jobs if they run once every minute.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 7, 2012 at 12:55 pm
Did you ever find a solution here? We upgraded to 11.0.2100.60 recently and are having the same problem. I see history from before the upgrade, but no history after the upgrade is listed. I am using SSMS 11.0.2100.60 to connect to the server as well. Using sp_help_jobhistory I don't see any results from after the upgrade (I still see them before the upgrade even though they should have aged out based on the history setting for SQL Server Agent), yet I know the jobs are running still and still get notification if they happen to fail.
September 10, 2012 at 1:55 pm
Truthfully, I never found a solution. I've increased the setting size in the SQL Agent w/out much success.
Would really like to know how to view the history, but right now, I don't have an answer.
November 9, 2012 at 6:52 am
Discovered my problem this morning, a broken trigger on sysjobhistory. I probably should have had the foresight to see this coming when I created that trigger. Might be worth checking into for your situation as well?
May 6, 2014 at 11:55 am
I also have similar issue with the SQL Job history,the issue is whenever a job fails the job history would show the error due to which the job failed ,but after the 2012 upgrade the job history just shows that the package execution failed and does not show the detailed error.
Has anyone experienced this and got a work around?
August 5, 2014 at 2:34 pm
I'm having the same issue, I can see the history in the select * from msdb.dbo.sysjobhistory but not in the GUI when selecting the job.
any one else out there have any suggestions? Im on SQL server 2012 as well fresh install
August 5, 2014 at 3:13 pm
This somewhat relates to the no history issue. I see a few jobs that only have a few rows of history, they are only failures and nothing recent. However a job is running right now and set to run every day, however the next run column says 6/19/2014 which passed months ago. I don't get how this next run time is calculated and was wondering if someone did.
To simplify I'll focus on this one job that's currently executing. It runs once every 24 hours between 4AM and 11:59PM. The last execute time is all the way back in January. The job runs a while loop with a SP that takes parameters. However the criteria is WHILE 1 = 1 for the loop, and the next line down after the SP is a wait for delay of 20 minutes, then the end of the loop. There is no break statement, so this makes me think the job is constantly going, even after the times between 12:00AM and 3:59AM when it's not scheduled. Is this true, will a job keep running outside of it's scheduled window? Could I be seeing such odd history because the last time the job was executed was back in January and it's been running constantly since? This doesn't really answer my first question about the next run column but I am seeing several jobs that have way back start dates, little to no run history, and next run dates that have already passed long ago. I'm wondering if this is what happens when a job never really get's a chance to complete.
Thanks! here is the job step is
While 1 = 1
begin
Exec Shopping.DeleteProducts 5, '00:00:01'
Waitfor Delay '00:20:00'
end
January 4, 2016 at 9:03 am
If you have a comma in the name, remove it. That resolved the error for me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply