SQL Server - Is it possible to find the size actually used in an MDF or LDF file

Question

When adding a .MDF (.NDF) or .LDF file to a SQL Server, we have the option to set its initial size, auto-growth, and incremental (percent or absolute).

After the database is in operation for a while, is it possible find how much of the actual size is used by the data? For example, if the actual size of the file is 5M, but only 2M is used to store the data, the file can still take 3M of data before it needs to grow.

I need a way to find out the "2M" (used size) in the total current size (5M) of the file.

Answer 1

After some research, I noticed the FILEPROPERTY function.

SELECT FILEPROPERTY(name, 'SpaceUsed') spaceUsed, * 
FROM sysfiles

It seems to give me how much being used within the current size of the file. For example, if the current size of the file is 5M, the FILEPROPERTY() may give me 2M, which means that the file can still take 3M of data before it needs to grow.

If anyone can confirm with me, I will mark it as the answer.

Answer 2

sys.database_files (Transact-SQL)
Contains a row per file of a database as stored in the database itself. This is a per-database view. ...

size     | int | Current size of the file, in 8-KB pages.
         |     | For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.
---------+-----+------------------------------------------------
max_size | int | Maximum file size, in 8-KB pages.
         |     | Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.

and

FILEPROPERTY (Transact-SQL) Returns the specified file name property value when a file name and property name are specified. ...

SpaceUsed | Amount of space that is used by the specified file. | Number of pages allocated in the file

Use them like this:

SELECT 
    name, 
    size, 
    FILEPROPERTY(name, 'SpaceUsed') AS SpaceUsed, 
    size - FILEPROPERTY(name, 'SpaceUsed') As UnusedSize
FROM 
    sys.database_files