Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1385
  • Last Modified:

Maintenance Jobs failing strangely

Last night, the Transaction log backup step of our log shipping setup started failing on one of the servers.  
The generic message is:

sqlmaint.exe failed [SQLSTATE 42000] [Error 22029].

When I paste the maintenance plan job step (execute master.dbo.xp_sqlmaint ...) into query analyzer and run as SA, the bizarre error is

--[Microsoft SQL-DMO (ODBC SQLState: 22003)]
--Error 220: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error
--for data type smallint, value = 46080.                                                                                          
--[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for data type smallint, value = 46080.                                                                                                                                                
--[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.                                                                                                                                                                              

arithmetic overflow ?  What is this trying to do ?

Thanks,
JK
0
JaffaKREE
Asked:
JaffaKREE
  • 8
  • 6
  • 2
  • +1
1 Solution
 
ShogunWadeCommented:
It is trying to convert a value (possibly an int datatype) into a smallint and max smallint is 32,767.   I sispect that you have an incorrect datatype in the destination server.
0
 
JaffaKREEAuthor Commented:
This is a log shipping transaction log backup job.  All it should be doing is backing up the transaction log.

0
 
ShogunWadeCommented:
ah ok sorry didnt properly read the question.

I dont suppose in that case anyone has been doing any DBCC checkident   with reseed options  ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JaffaKREEAuthor Commented:
Nope.  The server was rebooted last night and the Maintenance jobs (anything involving sqlmaint) started failing.   This server is rebooted regularly, so that isn't unusual.  

0
 
Anthony PerkinsCommented:
I suspect you have added computed columns or indexed views.  If that is the case let us know and I can post the relevent article, but you should know that is "by design".
0
 
Anthony PerkinsCommented:
Never mind, I just read your question <g>
0
 
JaffaKREEAuthor Commented:
I can't imagine what this thing is doing.  there are not even any smallint columns in any of the msdb log shipping tables.

all the log shipping plans on other server are still working correctly, this would seem to rule out any cross-server communication problem or issues with the monitor/destination.

0
 
ShogunWadeCommented:
"When I paste the maintenance plan job step "    can you post this command pls?
0
 
JaffaKREEAuthor Commented:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID D9CCECC6-CAFB-4EC1-B2CE-461C332D3D1C -WriteHistory  -BkUpMedia DISK -BkUpLog "\\Cvntsqlbackup\SQLBACKUP" -DelBkUps 1DAYS -BkExt "TRN"'
0
 
JaffaKREEAuthor Commented:
some more information...

mydomain/Administrator is a Domain admin account.  It is also a System Administrator on all SQL Servers.

Services:
MSSQLSERVER - log on as mydomain/Administrator
SQLSERVERAGENT - log on as mydomain/Administrator

0
 
JaffaKREEAuthor Commented:
oh cool, I get the same error when clicking Properties on these databases.

Microsoft SQL-DMO (ODBC SQLState: 22003)

Error 220: Artihmetic overflow error for data type smallint, value = 46080.
0
 
ShogunWadeCommented:
have you tried  checking the msdb integrity

DBCC checkdb('msdb')
0
 
JaffaKREEAuthor Commented:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
ShogunWadeCommented:
"oh cool, I get the same error when clicking Properties on these databases.
Microsoft SQL-DMO (ODBC SQLState: 22003)
Error 220: Artihmetic overflow error for data type smallint, value = 46080."

Ok.    Im banging my head against a wall @ the mo cos I have seen this before myself but im damned if i can remember what it was.

Ill have a think and if i remember i'll let you know.  Sorry I cant be of much more help. :(
0
 
JaffaKREEAuthor Commented:
Ok, this is the dumbest SQL problem I've ever seen.

I was able to track down the source of the problem by querying sysdatabases.  select * from sysdatabases would return some rows, then give that same smallint overflow error.  It stopped after returning msdb's data.  

The next db was Northwind, which was offline because it's Northwind.  For some reason, Northwind being offline was causing these arithmetic overflow errors.

I suspect that one of the smallint fields in sysdatabases was being set incorrectly.  The sqlmaint jobs and properties must query sysdatabases before loading the window.

All i did to fix it was bring Northwind online.

I appreciate your help, the msdb suggestion got me looking at the system tables more closely.

0
 
ShogunWadeCommented:
Thanks for posting the solution.    Glad you got it sorted.
0
 
Kevin3NFCommented:
9 year old thread, and it helped me today.   Awesome :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now