when I run the following query on MY_Database
select * from sys.sysfiles
I get the following results:
But when I run a dynamic query that gets the percent of free space I get:
Msg 8115, Level 16, State 7, Line 93 Arithmetic overflow error
converting numeric to data type numeric.
DECLARE @command NVARCHAR(MAX)
SELECT @command = 'SELECT db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
AS decimal(5,2))) AS varchar(8)) + ' + '''' + '''' + ' AS FreeSpacePct
FROM sys.sysfiles S'
exec sp_executesql @statement = @command
I am struggling to find the reason of the arithmetic overflow.
why is it happening?
Why divide by 128? It is because both sys.sysfiles and FILEPROPERTY give the number of 8K
pages, not MB, and to convert from 8K pages to MB you divide by 128
as it is explained here
Why is it dynamic?
Because I actually get the values from each database using sp_ForEachDB as you can see on the example below:
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use ' + '?' + ' SELECT db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
--,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
--' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
--AS decimal(4,2))) AS varchar(8)) + ' + '''' + '''' + ' AS FreeSpacePct
FROM dbo.sysfiles S'
EXEC sp_ForEachDB @command
Best Answer
Most likely the metadata is returning some unexpected values that your code cannot handle. For example:
...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:
Main advantages:
STR
formats the result and does not raise an error on overflowA dynamic SQL version (to collect information for all databases):
Usual caveats about using
sp_MSforeachdb
.