none
Shrinking Database - Arithmetic overflow error converting numeric to data type numeric RRS feed

  • Question

  • I was confused after looking into the issue "Arithmetic overflow error converting numeric to data type numeric."

    I know that this is due to the precision not set / supported for the decimal data type. But the strange is that the same statements are working more than a years, recently stopped working and throws a error message after Shrinking of that SQL Database.

    Will it be possible !!! Does the Shrinking of Database have any relationship with this issue ?

    Kindly share your thoughts and views...


    MJ - Man of Joy (Rajkumar) Techno Geek

    Monday, December 24, 2012 9:59 AM

Answers

  • The default scale for decimal data type is 0. Seems that you needed to set it to 1. What about numbers that you have been storing? It is not related to shrink operation.
    • Edited by Amin Sobati Monday, December 24, 2012 10:54 AM
    • Marked as answer by MJ Techno Geek Monday, December 24, 2012 11:04 AM
    Monday, December 24, 2012 10:54 AM

All replies

  • Hi,

    Does DBCC CHECKDB return any error? Have you installed new patch or service pack?

    Monday, December 24, 2012 10:06 AM
  • CHECKDB found 0 allocation errors and 0 consistency errors in database 'Prod'.

    No Errors, we didn't done any patch or service pack...

    What we did is only Shrinking of Database.. Earlier the Log Faile Size is around 71GB. 

    Once, the database shrinking completed it becomes 1MB. 

    Also, we facing the issue in last two days. I just want to confirm / know about that the Shrinking is related to it.


    MJ - Man of Joy (Rajkumar) Techno Geek

    Monday, December 24, 2012 10:33 AM
  • Can you detect the exact statement which raises this error? You can get help from tracing the particular user in Profiler (According to its SPID or Login Name or HostName...)
    • Edited by Amin Sobati Monday, December 24, 2012 10:46 AM
    Monday, December 24, 2012 10:45 AM
  • Arithmetic overflow error converting numeric to data type numeric

    Above is the error which I am getting while executing that store procedure... We have a backup of the production application as test. It was working fine in that database, Only on the production database.

    I have resolved the issue by altering the datatype declaration. I want to know the reason for it... Why not on the Test Server and not faced such issue more than years. Same Figures, Same Values.....

    Store Procedure:

    Earlier Declaration: declare @Weight decimal

    Altered Declaration: declare @Weight decimal(4,1)


    MJ - Man of Joy (Rajkumar) Techno Geek

    Monday, December 24, 2012 10:50 AM
  • The default scale for decimal data type is 0. Seems that you needed to set it to 1. What about numbers that you have been storing? It is not related to shrink operation.
    • Edited by Amin Sobati Monday, December 24, 2012 10:54 AM
    • Marked as answer by MJ Techno Geek Monday, December 24, 2012 11:04 AM
    Monday, December 24, 2012 10:54 AM
  • Shrinking the database or log file will not cause this kind of issue, there must some other change which should have caused this issue.

    How is the value to this variable passed? possible the value passed was causing this issue and the value causing the error might not have been passed earlier. It would not be possible to tell without any traces for good and bad captures with same value passed to this variable.

    Monday, December 24, 2012 10:57 AM
  • Thanks for replying Keerthi Deep...

    I have checked the values of parameter. Issue is only with the declaration of decimal. My confusion was it occurred only before two days...

    Thanks Amin Sobati 


    MJ - Man of Joy (Rajkumar) Techno Geek

    Monday, December 24, 2012 11:06 AM