SQL Server 查询数据库及数据文件大小的方法

1. 使用系统存储过程 sp_spaceused

查询特定数据库的大小

USE [数据库名]
EXEC sp_spaceused

查询所有数据库的大小

EXEC sp_msforeachdb 'USE [?]; EXEC sp_spaceused'

2. 查询系统视图 sys.database_files

查看当前数据库的详细文件信息

USE [数据库名]
SELECT 
    name AS [文件名],
    type_desc AS [文件类型],
    physical_name AS [物理路径],
    size/128.0 AS [文件大小(MB)],
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已用空间(MB)],
    size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [可用空间(MB)],
    growth/128 AS [自动增长(MB)]
FROM sys.database_files

3. 查询系统视图 sys.master_files

查看所有数据库的文件信息

SELECT 
    DB_NAME(database_id) AS [数据库名],
    name AS [逻辑文件名],
    type_desc AS [文件类型],
    physical_name AS [物理路径],
    size/128.0 AS [文件大小(MB)],
    CASE max_size 
        WHEN 0 THEN '不自动增长'
        WHEN -1 THEN '增长到磁盘满'
        ELSE CAST(max_size/128.0 AS VARCHAR) + ' MB'
    END AS [最大大小]
FROM sys.master_files
ORDER BY database_id, file_id

4. 综合查询所有数据库大小

查询所有数据库的总大小

SELECT 
    name AS [数据库名],
    SUM(size)/128.0 AS [总大小(MB)],
    SUM(CASE type WHEN 0 THEN size ELSE 0 END)/128.0 AS [数据文件大小(MB)],
    SUM(CASE type WHEN 1 THEN size ELSE 0 END)/128.0 AS [日志文件大小(MB)]
FROM sys.master_files
GROUP BY database_id, name
ORDER BY [总大小(MB)] DESC

5. 查看数据库空间使用详情

详细的空间使用分析

SELECT 
    DB_NAME() AS [数据库名],
    (SELECT SUM(size)/128.0 FROM sys.database_files WHERE type = 0) AS [数据文件总大小(MB)],
    (SELECT SUM(FILEPROPERTY(name, 'SpaceUsed'))/128.0 
     FROM sys.database_files WHERE type = 0) AS [数据文件已用空间(MB)],
    (SELECT SUM(size)/128.0 FROM sys.database_files WHERE type = 1) AS [日志文件总大小(MB)],
    (SELECT SUM(FILEPROPERTY(name, 'SpaceUsed'))/128.0 
     FROM sys.database_files WHERE type = 1) AS [日志文件已用空间(MB)]

6. 使用动态管理视图 (DMV)

监控数据库文件增长

SELECT 
    DB_NAME(database_id) AS [数据库名],
    file_id,
    num_of_reads,
    num_of_writes,
    size_on_disk_bytes/1024/1024 AS [磁盘大小(MB)],
    io_stall_read_ms,
    io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

7. 实用脚本 - 生成数据库大小报告

SELECT 
    db.name AS [数据库名],
    mf.name AS [文件逻辑名],
    mf.physical_name AS [物理文件路径],
    CAST(mf.size/128.0 AS DECIMAL(10,2)) AS [当前大小(MB)],
    CAST(mf.size/128.0 - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS INT)/128.0 
         AS DECIMAL(10,2)) AS [可用空间(MB)],
    CASE mf.is_percent_growth 
        WHEN 1 THEN CAST(mf.growth AS VARCHAR) + '%'
        ELSE CAST(mf.growth/128 AS VARCHAR) + ' MB'
    END AS [增长方式],
    CASE mf.max_size 
        WHEN 0 THEN '不增长'
        WHEN -1 THEN '无限制'
        ELSE CAST(mf.max_size/128 AS VARCHAR) + ' MB'
    END AS [最大大小]
FROM sys.databases db
INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE db.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY db.name, mf.type

8. 使用 SSMS 图形界面查看

注意事项：

这些方法可以根据需要选择使用，通常建议使用 sys.master_files 视图查询所有数据库信息，使用 sys.database_files 查询特定数据库的详细信息。