asked
SQL Server Jobs not logging - getting arithmetic overflow errors in the logs
Greetings, My SQL Server jobs are no longer able to log entries to the "Job History" logs. When I view the history of a job, there are no entries. If I go to the SQL Server Agent "Current" log, all I see are the following errors being repeated: [298] SQLServer Error: 3606, Arithmetic overflow occurred. [SQLSTATE 01000] (ConnExecuteCachableOp) [298] SQLServer Error: 8115, Arithmetic overflow error converting IDENTITY to data type int. [SQLSTATE 22003] (ConnExecuteCachableOp) Most of the jobs are related to SQL Server log shipping (this is on our secondary server). I'm wondering if it's possible that some kind of internal identity column for the log entries themselves, or for the job history, has reached the limit for the column's datatype (int, presumably). After cycling the logs or restarting the SQL Server Agent service, I do see the following in the log: [129] SQLSERVERAGENT starting under Windows NT service control There are a few other service related entries from the same time in the logs. So, logging itself seems to be possible, so my guess is that this issue is somehow related to the job history itself. Anytime I run a job (either by schedule or manually) I get a lot of the repeated arithmetic overflow errors. The contents of the job (the SQL commands or OS log shipping commands) will run fine if I run them manually. If anyone has any suggestions of where I should look, I'd appreciate hearing from you. Thanks, Bill
I'm going to answer my own question (if that's permitted). After a lot of digging around, I started looking at the msdb to see where the job history was stored. When I checked the current identity seed for the instance_id column in sysjobhistory, I found the value was: 2,147,483,647. Yes, the max value for int. dbcc checkident(sysjobhistory) So, I reset the seed to 1, via: dbcc checkident(sysjobhistory, reseed, 1) Now everything is working properly again! (there were no records in the sysjobhistory table, since they get cleared out on a regular basis, so resetting the seed to 1 was not an issue).
Absolutely it's permitted. You can also mark it as the final solution be clicking on the check mark next to it.
14 People are following this question.
