SQL Server 查询数据库及数据文件大小的方法
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. 直接使用SQL</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 查询当前数据库大小</a></li><li><a href="#_lab2_0_1">1.2 查询所有数据库的大小</a></li><li><a href="#_lab2_0_2">1.3 查询每个数据库的详细文件大小</a></li></ul><li><a href="#_label1">2.调用存储过程</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">2.1 查询所有数据库的大小（数据和日志文件分开显示）</a></li><li><a href="#_lab2_1_4">2.2 查询所有数据库的大小（数据 + 日志）合并显示</a></li><li><a href="#_lab2_1_5">2.3 查询所有数据库的大小（输出 GB、加上磁盘路径等信息）</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>1. 直接使用SQL</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 查询当前数据库大小</h3>
<div class="jb51code"><pre class="brush:sql;">EXEC sp_spaceused;
</pre></div>
<p>说明：<br />该命令会返回当前数据库的总大小、未使用空间等信息。<br />前提是你已经 USE 到目标数据库中。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>1.2 查询所有数据库的大小</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
DB_NAME(database_id) AS DatabaseName,
CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB
FROM
sys.master_files
GROUP BY
database_id
ORDER BY
SizeMB DESC;
</pre></div>
<p>说明：<br />包括数据文件（.mdf）和日志文件（.ldf）的大小。<br />size 单位是 8KB，所以需要乘 8 后再除以 1024 转换为 MB。</p>
<p>如果需要输出 GB 大小，可使用如下SQL:</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
DB_NAME(database_id) AS DatabaseName,
CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(18,2)) AS TotalSizeMB,
CAST(SUM(size) * 8 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS TotalSizeGB
FROM
sys.master_files
GROUP BY
database_id
ORDER BY
TotalSizeMB DESC;
</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>1.3 查询每个数据库的详细文件大小</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
DB_NAME(mf.database_id) AS DatabaseName,
mf.name AS LogicalName,
mf.type_desc AS FileType,
mf.physical_name AS FilePath,
CAST(mf.size * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB
FROM
sys.master_files mf
ORDER BY
DatabaseName, FileType;
</pre></div>
<p>说明：<br />可以看到每个文件的逻辑名称、文件类型（数据或日志）、路径及大小。</p>
<p>如果你需要指定某个数据库名查询，也可以加上条件，例如：</p>
<div class="jb51code"><pre class="brush:sql;">WHERE DB_NAME(mf.database_id) = 'YourDatabaseName'
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>2.调用存储过程</h2>
<p>如果不想每次写SQL查询，建议写成可重复使用的存储过程，之后调用存储过程即可。</p>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.1 查询所有数据库的大小（数据和日志文件分开显示）</h3>
<p>如下存储过程可列出所有数据库的大小（数据和日志文件分开显示），单位为 MB：</p>
<div class="jb51code"><pre class="brush:sql;">--创建存储过程
USE master;
GO
CREATE PROCEDURE dbo.GetAllDatabaseSizes
AS
BEGIN
SET NOCOUNT ON;
SELECT
DB_NAME(mf.database_id) AS DatabaseName,
mf.type_desc AS FileType, -- ROWS = 数据文件，LOG = 日志文件
CAST(SUM(mf.size) * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB
FROM
sys.master_files mf
GROUP BY
mf.database_id,
mf.type_desc
ORDER BY
DatabaseName, FileType;
END;
GO</pre></div>
<p>调用方式如下：</p>
<div class="jb51code"><pre class="brush:sql;">EXEC dbo.GetAllDatabaseSizes;
</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2.2 查询所有数据库的大小（数据 + 日志）合并显示</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE PROCEDURE dbo.GetDatabaseTotalSizes
AS
BEGIN
SET NOCOUNT ON;
SELECT
DB_NAME(database_id) AS DatabaseName,
CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(18,2)) AS TotalSizeMB
FROM
sys.master_files
GROUP BY
database_id
ORDER BY
TotalSizeMB DESC;
END;
GO</pre></div>
<p>调用方式如下：</p>
<div class="jb51code"><pre class="brush:sql;">EXEC dbo.GetDatabaseTotalSizes;
</pre></div>
<p class="maodian"><a name="_lab2_1_5"></a></p><h3>2.3 查询所有数据库的大小（输出 GB、加上磁盘路径等信息）</h3>
<div class="jb51code"><pre class="brush:sql;">USE master;
GO
CREATE PROCEDURE dbo.GetDatabaseFileDetails
AS
BEGIN
SET NOCOUNT ON;
SELECT
DB_NAME(mf.database_id) AS DatabaseName,
mf.type_desc AS FileType, -- ROWS 或 LOG
mf.physical_name AS FilePath, -- 文件完整路径
CAST(mf.size * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB,
CAST(mf.size * 8 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS SizeGB
FROM
sys.master_files mf
ORDER BY
DB_NAME(mf.database_id), mf.type_desc;
END;
GO</pre></div>
<p>调用方式如下：</p>
<div class="jb51code"><pre class="brush:sql;">EXEC dbo.GetDatabaseFileDetails;
</pre></div>
頁:
[1]