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
LVL 6
JaffaKREEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Kevin HillSr. SQL Server DBACommented:
9 year old thread, and it helped me today.   Awesome :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.