Post reply

Sum of data and index space used not equal to fileproperty() spaceused

  • February 8, 2018 at 7:38 pm

    #410226

    Hi -- I'm doing a SSMA migration from Sybase to SQL and i'm midway to finishing. I'm just curious why the sum data and index space used has a huge discrepancy with what is reported in sys.database_files using fileproperty() SpaceUsed function?

    The sum in Object Explorer details is just 370 GB which is very close to the 500 GB size from Sybase. The file however is reported as 1.7 TB + already.

    I'm aware of DBCC UPDATEUSAGE and i'll run this after it completes, but i'm really curious why this is the case 🙂

    select name
    , size/128 as 'size_mb'        
    ,FILEPROPERTY(name, 'SpaceUsed')/128 as'spaceused'                    
    from sys.database_files

    name    size_mb    spaceused
    international    576512    575779
    international_log    512    75
    international2    576512    575883
    international3    576512    575932

  • February 9, 2018 at 9:12 am

    #1979264

    It's a surprise to me if fragmentation caused this. Is data compressed in Sybase, but not in SQL Server?

    GASQL.com - Focus on Database and Cloud

  • February 11, 2018 at 9:09 pm

    #1979376

    Thanks mate. I was surprised too that it turns out to be fragmentation. A quick alter table rebuild cleared the excess reserved sp_spaceused. It's still the same number of rows.

    I used a batch size of 500 rows as anything higher seems to cause an issue with the older version of SSMA i'm stuck with.

    Before



    After

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply