Sunday, February 6, 2011

sys.database_files: size column and why 128?

It came up while my class was being conducted. We were discussing of finding the size of files attached to a database and available free space of files. As usual I used my sample codes to show few things, this is one of them:

SELECT name, size/128 size FROM sys.database_files

Now, why 128? It did not come into my mind immediately Confused smile. Result of being a designer.

Size column shows current size of the file in 8KB pages. It is the number of pages that have been allocated. So, why 128? It is for converting to MB. There are 128 8KB pages in 1MB. Here is my altered code;

SELECT name, size/128 SizeInMB
    , FILEPROPERTY(name, 'SpaceUsed')/128 SpaceUsedInMB 
FROM sys.database_files
-- or
SELECT name, size*8 SizeInKB
    , FILEPROPERTY(name, 'SpaceUsed')*8 SpaceUsedInKB 
FROM sys.database_files

No comments: