June 17, 2004 at 5:12 am
Has anyone experienced a situation with SQL 2K 800.818(SP3) whereby all scheduled jobs still execute normally without any error messages whatsoever in the Agent Log, SQK Server Logs and event logs, but no history is written into msdb..sysjobhistory despite the fact that we have configured SQL Server Agent properties\Job system to limit size of job lot, set maximum job history log size (rows) to 30000, set maximum job history rows per job to 400 (from 300) and currently do not have any jobs with more than 301 rows and no more than 15005 rows in total?
We have four identically set-up servers and only one has the above issue.
Normall SQLAgent Job Manager fires up every minute and checks for scheduled job steps waiting to be executed, it verifies these and SQLAgent - TSQL JobStep.. then executes each job step. Immediately following this SQLAgent Job Manager should then attempt to write the results of executing the job step to sysjobhistory using sp_sqlagent_log_jobhistory. However on the server in question, SQLAgent Job manager does not do this but simply on the next minute checks and executes any sheduled job/steps, therefore all job/steps are executed without error bu not recorded in the history associted with each job in sysjobhistory. Absolutely nothing is written into sysjobhistory.
We can still write to sysjobhistory manually without any issues using sp_sqlagent_log_jobhistory.
Does anyone know of a cause and/or a resolution to the above issue?
All ideas will be appreciated.
Overview of orws per job in sysjobhistory for information:
Associated_rows job_id
--------------- ------------------------------------
132 9D383AE7-0B4D-4CEE-9F6A-06AC16BAD5B6
300 0A42B2E3-46D9-4357-ACDE-0898F279C320
300 B5E749E0-317C-4CA5-A49C-0B952AE1E6BD
300 F0C15DAD-EA1A-47A7-BD41-0E5254890125
300 4DE6802B-62FB-4ED0-A3FC-1521185A2742
300 B174B6F7-6235-41B9-96BE-16DE938FA11B
74 D063703B-36FD-44AD-A381-185907948D4C
300 39376288-8B78-4EA8-ACEB-1C181419430B
132 69E7BB7A-B445-4765-B7A7-1E1165FD0B13
300 998A4E2A-58AC-4AE3-98B7-228CF35C90D8
300 E87DC3CB-757F-48C2-BD60-2D7CBD267A3A
300 02E03F60-DCBD-42B2-A706-2DF0135F22D2
300 62EDAA0B-65E7-46C3-ABB0-2FA0587C4632
120 934C0163-4F24-4206-A4F2-306791BEB5AC
300 8CE041C0-5BA9-483D-8B6C-328406595289
300 B0C92D3D-F5BC-4235-99D5-3850EA39E8A6
300 B3475D50-1D65-4532-8D9B-3D8EF346AE08
300 A2E91EDF-D666-4104-848E-3EB005EC27C9
300 A2E00B80-75A7-43B7-BE75-43D4F85459A2
300 81FAD4C6-BEC0-4756-8CE2-44A54E870B9A
300 EA101AB4-EC41-49EE-8089-456D411B6F4E
34 D1CCD250-D03A-4BE8-B6B0-45B62F84239F
300 82002472-45EF-4823-A2AC-475518700195
300 CC7A71E3-C0FE-4A76-BD77-4AB6B1CA426E
134 55A68C1A-4771-4477-8329-55AEE075A7B8
300 E985047A-E77E-4F54-81AE-55C94F7287C9
300 AAF8AC7A-EC93-4DF0-9DC4-5759D6490B9B
300 5708F0C0-291B-4F7F-ABF9-59051CA905CA
300 A961306F-7B6A-430F-96BB-67FE71DED1C8
300 F20FF508-4888-4951-BC63-6C37AED2DCBE
300 D19CE3D3-38E8-4751-9445-6D0DB5E165C3
10 A2AC4415-8805-41CF-B7A5-776D42D769D6
300 DE792621-F98C-40C4-AB4B-78D5A28423D2
132 188E604F-EA27-4804-9B46-7C5D585E55FE
300 79E6B04C-4ACE-4051-B0B9-857ED1F7B32E
6 86A25711-4209-4534-B67B-86FDA338FCDF
300 0CB81FD5-4DFA-4C28-BAF5-8BEC5CBB0E9A
300 2DC9B249-F9BE-4C61-9635-8F763D5982A3
300 8BE97017-9A8D-45E6-811F-91B5A71B9B4A
300 8D461EF2-AC2E-43E0-A758-933FA244E5B5
300 1E8B9C00-79F8-42D4-8A0F-9F81B14B8C49
260 8CF7817E-0B2A-4FE8-8BD7-A2E18889448E
300 8E40120E-1935-4877-A1FE-A466B8932A31
30 1814541B-822F-4BB4-AC72-A474A0298119
300 C7477B88-B4E8-4910-B20F-A7479B6BC3C7
16 EAB96E27-FA05-4452-9F9F-AB0BB4004BF1
300 9B78FAC9-3D44-4E7D-9714-BBBCA26783F1
300 D5110B73-9C4F-41A5-85BE-BDD779C04D6E
300 A79920A3-48AF-48DC-A279-BDED7BE19E01
300 C91FD222-E8F1-4E05-9EA2-C0788A1A97D0
300 1576B99C-E6CF-4173-B058-C4755E7B58CB
132 62D0D7BD-5D88-4577-A8CD-CE5B150624A2
172 068E736E-611C-462E-BB2E-D01D543FD53E
300 DCD6F92B-F0FE-4FC6-80CF-D1E1D2C8C643
300 23499201-EC4E-4A10-9F2C-D3449EAC4587
74 36E90415-AC44-473E-9BB5-D74F2C012ED9
82 D3D3EE0B-0543-4227-8C9B-D79207E018EB
301 D2B429E6-A496-488E-942A-DA1F95D3F0DD
170 D27977E8-15FA-47B6-A4B6-DC3724AD5ED3
6 D758ECA5-DBFB-4008-8ABB-E7511F27B580
300 1424800A-7983-4F15-B0D4-F1F86773EA66
300 3D32E4D6-B280-4724-ABD2-F376A283890E
88 3026F9CA-6D8E-4E3B-8573-FD1C845C51BD
Total Rows
-----------
15005
TIA
Philip
June 17, 2004 at 5:35 am
I have run into something similar in the past and had to increase the job history log. Please see below
To resize the job history log
Good Luck
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 17, 2004 at 6:24 am
Thanks AJ,
As I think I mentioned in my introduction, we had actually tried these angles already, in fact we even cleared all history from the log after backing it up to see whether this would make any difference but it did not, alas!
I even used the profiler to get a better understanding, but since it is the sqlagent.exe programme that ultimately executes the procedure that writes the results into history you cannot trace the reason it does not execute sp_sqlagent_log_jobhistory.
I think you might find that the maximum job history log size in rows and maximum jobhistory rows per job are normally only interrogated from the registry during the execution of sp_sqlagent_log_jobhistory and procedures called from within it.
I need to understand why the SQLAgent Job manager is not attempting to call sp_sqlagent_log_jobhistory.
Thanks again for your response.
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply