asked
why are file sizes from sys.index different to sys.database_files
Hi, I have 2 queries (shown below) to find the size of the data files in my database. I expected them to return the same values, but they don't. For my Data file the first query returns SpaceUsedMB as 26.56 while the second query return 4.55. Why the difference - what am I not taking into consideration with the second query that is covered by the first? Thanks in advance for any help. SELECT name AS FileName , size / 128.0 AS CurrentSizeMB , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB , CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS SpaceUsedMB FROM sys.database_files WHERE [type] = 0 ORDER BY name; SELECT RTRIM(d.Name) AS dataFileName , ( d.size / 128.0 ) AS CurrentSizeMB , ( d.size / 128.0 ) - ( SUM(a.used) / 128.0 ) AS FreeSpaceMB , ( SUM(a.used) / 128.0 ) AS SpaceUsedMB FROM sysindexes a INNER JOIN dbo.sysobjects b ON
a.Id =
b.Id INNER JOIN dbo.sysfilegroups c ON a.GroupId = c.GroupId INNER JOIN dbo.sysfiles d ON c.groupid = d.groupid --order by used desc GROUP BY d.Name , d.size ORDER BY d.Name
Rows in sysindexes where indid=1 reports in the 'used' column the number of pages for all data and all indexes, so adding this to other (indid between 2 and 250) used values will give an incorrect picture. Reference :
http://support.microsoft.com/kb/90758 Either use the first query, or the procedure `sp_spaceused` to get the correct values
Many thanks. Looking at that article and your answer it sounds like I'm not filtering out enough records in the second query. However if that's the case I would expect the used value to be higher in the second query - whereas it's higher in the first. I can go ahead and use the first query - I'm just curious to understand the differences.
Actually it depends on the version of SQL you are using. Since SQL2005, the underlying tables used by sp_spaceused are sys.partitions and sys.allocation_units - rather than the SQL 2000 system table sysindexes. So trying to tie these 2 queries up, is a little like comparing apples to oranges.
14 People are following this question.
