选择 SQL Server 数据库大小

Question

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

我用这个：

 use "MY_DB"
exec sp_spaceused

我懂了 ：

 database_name   database_size   unallocated space
My_DB           17899.13 MB 5309.39 MB

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

我也试过这段代码：

 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

所以它有效，但也许有适当的方法来获得这个？

原文由 Adeel ASIF 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

试试这个——

询问：

 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 年 1 月 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

原文由 Devart 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

如果您想获得真实数据的大小，您只需要过滤“在线”数据文件。否则，您可以获得与您在 GUI 中看到的大小显着不同的大小。一些鬼|旧的垃圾记录可以在 sys.master_files 中，例如“DEFUNCT”数据文件。

     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

原文由 GLeb 发布，翻译遵循 CC BY-SA 4.0 许可协议