SQL Server-是否可以找到MDF或LDF文件中实际使用的大小 [英] SQL Server - Is it possible to find the size actually used in an MDF or LDF file

问题描述

添加 .MDF （ .NDF ）或 .LDF 文件添加到SQL Server，我们可以选择设置其初始大小，自动增长和增量（百分比或绝对值）。

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).

在数据库之后如果运行了一段时间，是否有可能发现数据使用了多少实际大小？例如，如果文件的实际大小为5M，但仅使用2M来存储数据，则文件在需要增长之前仍可以获取3M数据。

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.

我需要一种方法来找出文件当前总大小（5M）中的 2M（使用的大小）。

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

推荐答案

sys.database_files（Transact-SQL）

包含数据库本身存储的每个数据库文件行。这是每个数据库的视图。
...

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.

和

FILEPROPERTY（Transact-SQL）
当指定文件名和属性名时，返回指定的文件名属性值。
...

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

中分配的页数，请按以下方式使用它们： / p>

Use them like this:

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

这篇关于SQL Server-是否可以找到MDF或LDF文件中实际使用的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！