Arithmetic overflow error

[ciarra41]

I'm getting this error 8115, which is the dreaded converting issue.
The problem is I'm not associating it to a particular value, just counting and using a mulitplier. If you remove those two lines where I got (fails here) it runs fine. I've tried converting it to a bigint, int, and isnumeric but no solution as of yet, any advice?



SELECT
obj.name as tblname,
i.name as indname,
i.type_desc,
count(*)AS Buffered_Page_Count,
case when ISNUMERIC(count(*) * 8192 / (1024 * 1024) ) <> 1 then NULL else count(*) * 8192 / (1024 * 1024) end as 'MB_size' (fails here)
--isnull(cast((cast(count(*)* 8192/(1024*1024)as FLOAT))as BIGINT),0) as MB_size (fails here)
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)---table objects
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2---table objects
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id() and obj.name not like 'sys%'
GROUP BY obj.name, obj.index_id , i.name,i.type_desc

 

[markros]

Try:

SELECT 
 obj.name as tblname,
i.name as indname,
i.type_desc,
count(*) AS Buffered_Page_Count,
cast(count(*) * 8192 as float)/ (1024 * 1024)  as 'MB_size'  
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
       FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)---table objects
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2---table objects
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id() and obj.name not like 'sys%'
GROUP BY obj.name, obj.index_id , i.name,i.type_desc

PluralSight Learning Library

 

[ciarra41]

Nope that did not work either. This is strange, all I'm doing is counting the rows.
Any other suggestions?

 

[ciarra41]

I think this got it; I did not get that error.

convert(bigint,CAST(count(*)AS bigint))* 8192 / (1024 * 1024)

 

[PDreyer]

Or change the multiplication to division i.e.
Count(*)/128

 

[gmmastros]

You can simplify this a bit by using the Count_Big function.

Another thing I noticed is that you will get integer math because you are using Count (which returns an int). Count_Big returns a bigint so that should solve your arithmetic overflow problem. However, you'll still get integer/(big int) math. You could solve this problem by converting to floating point math, like this:

Personally, I would suggest that you combine this idea with the suggestion by PDreyer and use this:

count_big(*) / 128.0  as 'MB_size'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom