Sybase arithmetic overflow with run script to check database data and log usage

motioneye
motioneye used Ask the Experts™
on
Hi,
I run a script below which always return error as below


SELECT "Database Name" = CONVERT(char(20), db_name(D.dbid)),
"Data Size" =
STR(SUM(CASE
WHEN U.segmap != 4 THEN U.size*(@@maxpagesize/1048576)
END
),10,1),
"Used Data" =
STR(SUM(CASE
WHEN U.segmap != 4
THEN size -
curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)
END
)
*@@maxpagesize/1048576,10,1),
"Data Full%" =
STR(100 *
(1 - 1.0 *
SUM(CASE
WHEN U.segmap != 4
THEN curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)
END
) / SUM(CASE
WHEN U.segmap != 4 THEN U.size
END
)
)
,9,1) + "%",
"Log Size" =
STR(SUM(CASE
WHEN U.segmap in (4, 7) THEN U.size*@@maxpagesize/1048576
END),10,1),
"Free Log" =
STR(lct_admin("logsegment_freepages",D.dbid)*@@maxpagesize/1048576,10,1),
"Log Full%" =
STR(100 * (1 - 1.0 * lct_admin("logsegment_freepages",D.dbid)
/ SUM(CASE WHEN U.segmap in (4, 7) THEN U.size END)),8,1) + "%"
FROM master..sysdatabases D,
master..sysusages    U
WHERE U.dbid = D.dbid
AND (((D.dbid > 3) AND (D.dbid < 31513) OR D.dbid = 2)  AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)



Msg 3606, Level 16, State 4:
Server 'Sybase_test', Line 1:
Arithmetic overflow occurred.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Principal Consultant
Most Valuable Expert 2012
Commented:
Think this is your problem:

     WHEN U.segmap != 4 THEN U.size*(@@maxpagesize/1048576)

Notice that every other time you do a size calculation, you don't have those parentheses. Try replacing it with:

     WHEN U.segmap != 4 THEN U.size*@@maxpagesize/1048576

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial