Geek Answers Handbook

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

When a file .MDF( .NDF) or is added to SQL Server .LDF, we can set its initial size, automatic growth, and incremental (percentage or absolute).

After the database has been running for some time, is it possible to find out how much the actual size is used by the data? For example, if the actual file size is 5M, but only 2M is used to store data, the file can still take 3M of data before it grows.

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

+4
source share
5 answers

sys.database_files (Transact-SQL)
, . ....

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.

FILEPROPERTY (Transact-SQL) ....

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

:

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

FILEPROPERTY.

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

, . , 5M, FILEPROPERTY() 2M, , 3M , .

- , .

+2

, , , db.

, model. , , model, 3MB

+1

- , , , . , 2gb, - 1gb, ~ 1gb . , .

0

SQL (.ldf)

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'
0

More articles:


All Articles