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

If you have created the database with your own specific initial size parameter then NO, there is no way of knowing it unless, if you have scripted the db creation.

Otherwise, generally the default initial size is considered to be same as model database. So, if it's default then you can check with model database initial size which generally is 3MB

Answer 2

Someone correct me if i'm wrong but for the data file I believe an *uncompressed backup should roughly correspond to the actual amount of data in the file. For example if the database is 2gb but the backup is 1gb you have ~1gb of data. As far as the log you're on your own.

Answer 3

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

Answer 4

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 5

How to find the SQL log file(.ldf) size

DECLARE @command varchar(1000)
SELECT @command = 'USE ? select [Name], physical_name [Path], 
CAST(size AS BIGINT)*8192 [TotalBytes], 
CAST(FILEPROPERTY(name,''SpaceUsed'') AS BIGINT)*8192 [UsedBytes], 
(case when max_size<0 then -1 else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
from sys.database_files'
Declare @dtTable table
(
[Name] varchar(max), 
physical_name varchar(max), 
[TotalBytes] varchar(max),
[UsedBytes] varchar(max),
[MaxBytes] varchar(max)
)
insert into @dtTable EXEC sp_MSforeachdb @command 
select * from @dtTable where physical_name like '%.ldf'
select * from @dtTable where physical_name like '%.mdf'