什麼佔用了我所有的空間？

我正在使用 SQL Server Express 2008 R2，第三方應用程序在此數據庫伺服器上擁有其數據庫。考慮到它實際上在做什麼，該數據庫似乎比我預期的要大得多。當我粗略計算表和索引使用的空間時，我得到了數據庫文件總大小的大約一半。

我想知道是什麼佔用了其餘的空間。我了解 SQL Server Express 限制為 10GB，這就是我擔心數據庫大約 2/3 已滿的原因。我也知道數據庫本身有可用空間，這沒問題。我不缺磁碟空間。

以下是我如何找到表和索引的大小。當我用Google搜尋它們時，我不太了解這些查詢，但它們似乎報告了我感興趣的數字：

-- Size of database and how much space is used
SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
  FROM sys.database_files AS df 
  WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
  FROM sys.database_files AS df 
  WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a 
 on p.partition_id = a.container_id;

結果：

DbSize   SpaceUsed  LogSize
-------  ---------  -------
7829.25    6130.09   611.13

好的，那麼 6.1 GB 的使用量是什麼？

以下查詢列出了所有表和索引的大小（source）：

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
           WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
           ELSE lob_used_page_count + row_overflow_used_page_count
       END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
   cte.TableName, 
   cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
   cast(((CASE WHEN cte.used_pages_count > cte.pages 
               THEN cte.used_pages_count - cte.pages
               ELSE 0 
         END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

如果我將所有大小超過 1mb 的表和索引加起來，總共不到 3GB。（我在上面的查詢中嘗試了 sum 但無法弄清楚）

如果我在這裡發布完整的輸出，它會很長而且很亂。

因此，似乎大約一半的預期可用空間被其他東西佔用了。

有什麼我在這裡遺漏的想法，或者我需要一種更好的方法來增加已用空間嗎？

上面“Henrik Staun Poulsen”的評論為我指明了正確的方向。我的數據庫使用的總空間有一半是單個 XML 索引。

我在這裡找到了一篇關於 XML 索引的好文章

https://www.red-gate.com/simple-talk/sql/database-administration/getting-started-with-xml-indexes/

我試圖重建索引

Alter Index <IndexName> on <TableName> Rebuild 

它執行了 5 分鐘，但這並沒有減小大小，所以我可能會堅持下去。

我將發布 Henrik 指向我的完整查詢，以防該連結將來消失。（我討厭那個）

   ;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
   SELECT  ps.[object_id] AS [ObjectID],
           ps.index_id AS [IndexID],
           NULL AS [ParentIndexID],
           NULL AS [PassThroughIndexName],
           NULL AS [PassThroughIndexType],
           SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
           SUM(ps.used_page_count) AS [UsedPageCount],
           SUM(ps.reserved_page_count) AS [ReservedPageCount],
           SUM(ps.row_count) AS [RowCount],
           SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                   AS [LobAndRowOverflowUsedPageCount]
   FROM    sys.dm_db_partition_stats ps
   GROUP BY    ps.[object_id],
               ps.[index_id]
   UNION ALL
   -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
   SELECT  sit.[parent_id] AS [ObjectID],
           sit.[object_id] AS [IndexID],
           sit.[parent_minor_id] AS [ParentIndexID],
           sit.[name] AS [PassThroughIndexName],
           sit.[internal_type_desc] AS [PassThroughIndexType],
           0 AS [InRowDataPageCount],
           SUM(ps.used_page_count) AS [UsedPageCount],
           SUM(ps.reserved_page_count) AS [ReservedPageCount],
           0 AS [RowCount],
           0 AS [LobAndRowOverflowUsedPageCount]
   FROM    sys.dm_db_partition_stats ps
   INNER JOIN  sys.internal_tables sit
           ON  sit.[object_id] = ps.[object_id]
   WHERE   sit.internal_type IN
              (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
   GROUP BY    sit.[parent_id],
               sit.[object_id],
               sit.[parent_minor_id],
               sit.[name],
               sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
       agg.[IndexID],
       agg.[ParentIndexID],
       agg.[PassThroughIndexName],
       agg.[PassThroughIndexType],
       OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
       OBJECT_NAME(agg.[ObjectID]) AS [TableName],
       SUM(CASE
               WHEN (agg.IndexID < 2) THEN agg.[RowCount]
               ELSE 0
           END) AS [Rows],
       SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
       SUM(agg.LobAndRowOverflowUsedPageCount +
           CASE
               WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
               ELSE 0
           END) * 8 AS [DataKB],
       SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
           CASE
               WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
               ELSE 0
           END) * 8 AS [IndexKB],
       SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
       SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
           agg.[IndexID],
           agg.[ParentIndexID],
           agg.[PassThroughIndexName],
           agg.[PassThroughIndexType],
           OBJECT_SCHEMA_NAME(agg.[ObjectID]),
           OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
      sp.TableName,
      sp.IndexID,
      CASE
        WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
        ELSE N'<Heap>'
      END AS [IndexName],
      sp.[PassThroughIndexName] AS [InternalTableName],
      sp.[Rows],
      sp.ReservedKB,
      (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
      sp.DataKB,
      (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
      sp.IndexKB,
      (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
      sp.UsedKB AS [UsedKB],
      (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
      sp.UnusedKB,
      (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
      so.[type_desc] AS [ObjectType],
      COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
      sp.[PassThroughIndexType] AS [IndexSecondaryType],
      SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
      sp.ObjectID
      --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
       ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
      ON  si.[object_id] = sp.ObjectID
     AND  (si.[index_id] = sp.IndexID
        OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
ORDER BY UsedGB Desc


11Jun19 Posted a question to Stack Exchange.  Didn’t use the exact queries below but something close.


16May19 This script returns information but it doesn’t add up to anything like the size of the db.

USE [MyArchive] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
--ORDER BY s.Name, t.Name
Order by Total_MB
GO

引用自：https://dba.stackexchange.com/questions/240247