I am struggling to find the reason of the arithmetic overflow. why is it happening?
Most likely the metadata is returning some unexpected values that your code cannot handle. For example:
-- Example values returned from sysfiles and FILEPROPERTY
DECLARE
@size integer = 1,
@spaceused integer = 10000;
-- The essence of the code in the question
SELECT
CAST
(
100 *
(
CAST
(
(
(@size/128.0 - @spaceused/128.0)/(@size/128.0)
)
AS decimal(5,2)
)
)
AS varchar(8)
) + '' AS FreeSpacePct;
...returns the error mentioned in the question, because the computed (negative!) value will not fit in decimal(5,2)
.
There are reasons why size might be reported as much lower than space used, including tempdb file growths, filestream files, bugs in older versions of SQL Server...too many to list. You could/should code defensively against this possibility (and also for offline/defunct files...and so on).
The question is tagged SQL Server 2014, so there's no need to use the deprecated sys.sysfiles
view (for backward compatibility with SQL Server 2000):
I might write this query as:
SELECT
DatabaseName = DB_NAME(),
[FileName] = DF.name,
FileType = DF.type_desc,
SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
FreeSpacePct = STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, 'SpaceUsed')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
Main advantages:
A dynamic SQL version (to collect information for all databases):
DECLARE @SQL nvarchar(2000);
SET @SQL = N'
USE ?;
SELECT
DatabaseName = DB_NAME(),
[FileName] = DF.name,
FileType = DF.type_desc,
SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
FreeSpacePct = STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, ''SpaceUsed'')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
';
DECLARE @Results AS table
(
DatabaseName sysname NOT NULL,
[FileName] sysname NOT NULL,
FileType nvarchar(60) NOT NULL,
SizeMB char(10) NULL,
SpaceUsedMB char(10) NULL,
FreeSpaceMB char(10) NULL,
FreeSpacePct char(7) NULL
);
INSERT @Results
EXECUTE sys.sp_MSforeachdb
@command1 = @SQL;
SELECT R.*
FROM @Results AS R
ORDER BY R.DatabaseName; -- Or whatever
Usual caveats about using sp_MSforeachdb
.
Best Answer
The rules that SQL Server uses to derive the type of literals are complex, with some odd behaviours that are maintained these days for backward compatibility. For example, the derived type may depend on whether simple parameterization is applied. There are other (undocumented) considerations as well.
With those caveats out of the way, the situation you face is one of the simpler ones. The untyped literals are typed as
integer
when they will fit in that data type. The data type of the result is as specified in * (Multiply) (Transact-SQL):The two data types are both
integer
(with obviously the same precedence), so the result is also typed asinteger
. When the result does not fit, an error may be thrown (depending on settings).When one of the literals is explicitly typed using
CAST
orCONVERT
as (for example)bigint
(which has a higher precedence thaninteger
) the result type will bebigint
, and no error occurs.The two larger constants in the question are typed as
numeric(24,0)
andnumeric(12,0)
respectively. The result of multiplying them is typed asnumeric(37,0)
in accordance with the rules fore1 * e2
shown in Precision, Scale, and Length (Transact-SQL):