选择SQL Server数据库大小

如何查询我的SQL Server以仅获取数据库的大小？

我用了这个：

use "MY_DB"
exec sp_spaceused

我得到了这个：

database_name   database_size   unallocated space
My_DB           17899.13 MB 5309.39 MB

它返回了几个我不需要的列，也许有一个技巧可以从这个存储过程中选择数据库_大小的列？

我也试过这个代码：

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'

它给了我这样的结果：

DatabaseName    Logical_Name    Physical_Name                    SizeMB
MY_DB           MY_DB           D:\MSSQL\Data\MY_DB.mdf          10613
MY_DB           MY_DB_log       D:\MSSQL\Data\MY_DB.ldf          7286

所以我写了这个：

SELECT SUM(SizeMB)
FROM (
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'
) AS TEMP

我得到了：1183

所以它是有效的，但也许有一个合适的方法来得到这个？

677773 次浏览
小开
最佳答案

试试这个-

查询：

SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id

产量：

-- my query
name           log_size_mb  row_size_mb   total_size_mb
-------------- ------------ ------------- -------------
xxxxxxxxxxx    512.00       302.81        814.81


-- sp_spaceused
database_name    database_size      unallocated space
---------------- ------------------ ------------------
xxxxxxxxxxx      814.81 MB          13.04 MB

功能：

ALTER FUNCTION [dbo].[GetDBSize]
(
@db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN


SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID(@db_name)
OR @db_name IS NULL
GROUP BY database_id

更新2016/01/22：

显示有关大小、可用空间和上次数据库备份的信息

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space


CREATE TABLE #space (
database_id INT PRIMARY KEY
, data_used_size DECIMAL(18,2)
, log_used_size DECIMAL(18,2)
)


DECLARE @SQL NVARCHAR(MAX)


SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')


EXEC sys.sp_executesql @SQL


SELECT
d.database_id
, d.name
, d.state_desc
, d.recovery_model_desc
, t.total_size
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_size
FROM (
SELECT
database_id
, log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files
GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

产量：

database_id name                             state_desc   recovery_model_desc total_size   data_size   data_used_size  log_size    log_used_size  full_last_date          full_size    log_last_date           log_size
----------- -------------------------------- ------------ ------------------- ------------ ----------- --------------- ----------- -------------- ----------------------- ------------ ----------------------- ---------
24          StackOverflow                    ONLINE       SIMPLE              66339.88     65840.00    65102.06        499.88      5.05           NULL                    NULL         NULL                    NULL
11          AdventureWorks2012               ONLINE       SIMPLE              16404.13     15213.00    192.69          1191.13     15.55          2015-11-10 10:51:02.000 44.59        NULL                    NULL
10          locateme                         ONLINE       SIMPLE              1050.13      591.00      2.94            459.13      6.91           2015-11-06 15:08:34.000 17.25        NULL                    NULL
8           CL_Documents                     ONLINE       FULL                793.13       334.00      333.69          459.13      12.95          2015-11-06 15:08:31.000 309.22       2015-11-06 13:15:39.000 0.01
1           master                           ONLINE       SIMPLE              554.00       492.06      4.31            61.94       5.20           2015-11-06 15:08:12.000 0.65         NULL                    NULL
9           Refactoring                      ONLINE       SIMPLE              494.32       366.44      308.88          127.88      34.96          2016-01-05 18:59:10.000 37.53        NULL                    NULL
3           model                            ONLINE       SIMPLE              349.06       4.06        2.56            345.00      0.97           2015-11-06 15:08:12.000 0.45         NULL                    NULL
13          sql-format.com                   ONLINE       SIMPLE              216.81       181.38      149.00          35.44       3.06           2015-11-06 15:08:39.000 23.64        NULL                    NULL
23          users                            ONLINE       FULL                173.25       73.25       3.25            100.00      5.66           2015-11-23 13:15:45.000 0.72         NULL                    NULL
4           msdb                             ONLINE       SIMPLE              46.44        20.25       19.31           26.19       4.09           2015-11-06 15:08:12.000 2.96         NULL                    NULL
21          SSISDB                           ONLINE       FULL                45.06        40.00       4.06            5.06        4.84           2014-05-14 18:27:11.000 3.08         NULL                    NULL
27          tSQLt                            ONLINE       SIMPLE              9.00         5.00        3.06            4.00        0.75           NULL                    NULL         NULL                    NULL
2           tempdb                           ONLINE       SIMPLE              8.50         8.00        4.50            0.50        1.78           NULL                    NULL         NULL                    NULL
小开

同时将结果与以下查询的结果进行比较

EXEC sp_helpdb @dbname= 'MSDB'

它产生类似于以下内容的结果

enter image description here

有一篇好文章-确定SQL Server数据库和数据库文件可用空间的不同方法。

小开

您可以按照这个链接检查此查询的工作方式。

IF OBJECT_ID('tempdb..#spacetable') IS NOT NULL
DROP TABLE tempdb..#spacetable
create table #spacetable
(
database_name varchar(50) ,
total_size_data int,
space_util_data int,
space_data_left int,
percent_fill_data float,
total_size_data_log int,
space_util_log int,
space_log_left int,
percent_fill_log char(50),
[total db size] int,
[total size used] int,
[total size left] int
)
insert into  #spacetable
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
x.[percent fill],y.[total size log],y.[space util],
y.[total size log]-y.[space util] [space left log],y.[percent fill],
y.[total size log]+x.[total size data] ''total db size''
,x.[space util]+y.[space util] ''total size used'',
(y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''total size left''
from (select DB_NAME() ''DATABASE NAME'',
sum(size*8/1024) ''total size data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=0
group by type_desc  ) as x ,
(select
sum(size*8/1024) ''total size log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=1
group by type_desc  )y'
select * from #spacetable
order by database_name
drop table #spacetable
小开

对我来说，在SQL Server 2012中计算SQL数据库大小非常完美。

exec sp_spaceused

enter image description here

小开 
SELECT      sys.databases.name AS [Database Name],
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Size]
FROM        sys.databases
JOIN        sys.master_files
ON          sys.databases.database_id=sys.master_files.database_id
GROUP BY    sys.databases.name
ORDER BY    sys.databases.name
小开

EXEC sp_spaceused @oneresultset = 1 在1行中显示所有结果

如果您只执行“ exec sp_spaceused ”，您将看到两行 在SQL Server Management Studio v17.9

中工作
小开 
SELECT
DB_NAME (database_id) as [Database Name],
name as [Database File Name],
[Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'
ELSE Type_Desc END,
size*8/1024 as 'Size (MB)',
physical_name as [Database_File_Location]
FROM sys.master_files
ORDER BY 1,3

输出

Database Name               Database File Name              Type                Size (MB)   Database_File_Location
--------------------------- ------------------------------- ------------------- ----------- ---------------------------------------------------------------
AdventureWorksDW2017        AdventureWorksDW2017            Data File(s)        136         E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017.mdf
AdventureWorksDW2017        AdventureWorksDW2017_log        Log File(s)         72          E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017_log.ldf
DBA_Admin                   DBA_Admin                       Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin.mdf
DBA_Admin                   DBA_Admin_log                   Log File(s)         8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin_log.ldf
EventNotifications          EventNotifications              Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications.mdf
EventNotifications          EventNotifications_log          Log File(s)         8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications_log.ldf
master                      master                          Data File(s)        4           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
master                      mastlog                         Log File(s)         2           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
model                       modeldev                        Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf
model                       modellog                        Log File(s)         8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf
msdb                        MSDBData                        Data File(s)        19          E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
msdb                        MSDBLog                         Log File(s)         13          E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
tempdb                      temp2                           Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf
tempdb                      temp3                           Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf
tempdb                      temp4                           Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf
tempdb                      tempdev                         Data File(s)        8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
tempdb                      templog                         Log File(s)         8           E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf
小开

检查Azure和本地的SQL Server中的数据库大小-

方法1—使用“ sys.database_文件”系统视图

SELECT
DB_NAME() AS [database_name],
CONCAT(CAST(SUM(
CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;

enter image description here

方法2–使用“ SP_空间已用”系统存储过程

EXEC sp_spaceused ;

enter image description here

小开

这里已经有很多很好的答案，但值得一提的是，有一种简单快捷的方法可以使用标准报告获得使用SQL Server Management Studio（SSMS）的SQL Server数据库大小。

要运行报告，您需要：

  1. 右键单击数据库
  2. 转到“报告”>“标准报告”>“磁盘使用率”。

它会打印一份漂亮的报告：

enter image description here

其中，保留的总空间是磁盘上数据库的总大小，它包括所有数据文件的大小和所有事务日志文件的大小。

在后台，SSMS使用dbo.sysfiles视图或sys.database_文件视图（取决于MSSQL的版本）和某种查询来获取总空间保留值：

SELECT sum((convert(dec (19, 2),
convert(bigint,SIZE))) * 8192 / 1048576.0) db_size_mb
FROM dbo.sysfiles;
小开

如果您只想检查单个数据库的大小，可以使用SSMS GUI来完成。

转至服务器资源管理器->；展开它->；右键单击数据库->；选择属性->；在弹出窗口中选择“常规”选项卡->；请参见大小

来源：_农行_0

小开

尝试下面的代码。它非常好的代码：

with CteDbSizes
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
dbFileSizes.[name] AS DatabaseName,
(select sum(size) from CteDbSizes where type = 1 and CteDbSizes.database_id = dbFileSizes.database_id) LogFileSizeMB,
(select sum(size) from CteDbSizes where type = 0 and CteDbSizes.database_id = dbFileSizes.database_id) DataFileSizeMB
from sys.databases dbFileSizes ORDER BY DataFileSizeMB desc

Result of the above query

小开

如果你想获得真实数据的大小，你只需要过滤“在线”数据文件。否则，您可能会得到一个与您在GUI中看到的大小明显不同的大小。某些GHOST|OLD垃圾记录可能位于SYS.MASTER_文件中，例如“失效”数据文件。

    with fs
as
(
select database_id, type, size * 8.0 / 1024 size, physical_name
from sys.master_files
where state_desc = 'ONLINE' -- ONLINE,RESTORING,RECOVERING,RECOVERY_PENDING,SUSPECT,,OFFLINE, DEFUNCT
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB,
(select sum(size) from fs where fs.database_id = db.database_id) TotalFileSizeMB
from sys.databases db
--order by database_id
order by TotalFileSizeMB desc

提交答案