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 . 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:
Post a Comment