5

when I run the following query on MY_Database

select * from sys.sysfiles

I get the following results:

enter image description here

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  
  • 4
    If you're looking for a way to calculate available space, don't reinvent the wheel. Here's my disk space monitoring script spaghettidba.com/2014/09/05/… I'm sure that there are many others. – spaghettidba May 31 '16 at 13:49
15

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.

4

Maybe you have forgotten that a file can have 100% free space?

In that case you need DECIMAL(5,2), not DECIMAL(4,2).

3

Run the query in SSMS and look at the actual execution plan. Highlight the Compute Scalar and look at its properties (F4). Look at the Defined Values property. On my system I got this:

[Expr1008] = Scalar Operator(db_name()), [Expr1009] = 
Scalar Operator(CONVERT(int,[Expr1013]/(128.0)CONVERT_IMPLICIT(numeric(10,0),
fileproperty([ReadReceipt].[sys].[sysprufiles].[lname],'SpaceUsed'),0)
/(128.0),0)), [Expr1010] = Scalar Operator(CONVERT(varchar(8),(100.)
*CONVERT(decimal(5,2),([Expr1013]/(128.0)-CONVERT_IMPLICIT(numeric(10,0),
fileproperty([ReadReceipt].[sys].[sysprufiles].[lname],'SpaceUsed'),0)
/(128.0))/([Expr1013]/(128.0)),0),0)+'')

There's a lot of CONVERT_IMPLICIT going on in there. Chances are one of your DBs is overflowing one of these intermediate calculations. I don't see an error on my small development box.

To debug I would comment out each of your calculated values in turn to see which is throwing the error. Then I'd filter out large DBs using WHERE. If it works for small DBs that would be a clue. Next strip the calculation down to its minimum and run it for just the the largest DB. Add the CASTs back one at a time and compare the Defined Values for arrangements where it works and where it fails.

My feeling is a single CAST to INT around the whole calculation is likely to be your best option.

This article state ARITHABORT setting has a bearing, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.