Arithmetic overflow error

[MeanJoeGreen13]

Hi,
Below query failed which executing in sql 2005 64Bit and large memory

select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.

but it work fine in my test server which running 2GB of ram with sql 2005

 

[PDreyer]

Change the calculation to
,count(*) / 128 as Buffer_Pool_MB

 

[mrdenny]

The problem would be that you are going beyond the bound of the INT data type which is what the count(*) is returning.

select count(*) AS Buffered_Page_Count
      ,cast(count(*) as bigint) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog