SQL 數據庫大小不正確，數據庫文件在空時增長

已解決 - 文章底部的解決方案

問題 我有一個數據庫數據文件在磁碟上顯示為 200GB，在數據庫屬性中分配了 200GB 空間。sp_spaceused 還顯示已分配 200GB。所有顯示幾乎為零的可用空間。

但是，查詢表和索引大小表明數據庫中只有大約 4GB 的數據。我相信這是正確的。我有一個執行 UPDATEUSAGE 的數據庫副本，它按預期更新了分配的和可用空間，顯示 196GB 的可用空間突然可用。這使我能夠將數據庫縮小到合理的 10GB。

但是，當我嘗試 UPDATEUSAGE 和 sp_spaceused @updateusage = ’true’ 在實時數據庫（在維護視窗中）上，它不會更新並顯示正確的可用空間。我嘗試回收 SQL Server 服務並再次執行這些命令，但沒有任何變化。

有沒有人看到分配和可用空間統計數據的問題，以及如何解決這個問題的任何想法？日誌表是空的，並且每小時備份一次，因此對這些數字的貢獻不大。

第二個問題顯然是要弄清楚為什麼數據庫會這樣增長。似乎 SQL 認為沒有可用空間，因此每次插入都會增加文件。我已經閱讀了一些 DDL 操作並且堆沒有正確更新未分配的空間，所以這是需要研究的。我檢查了每個堆使用和可用的空間，但這些似乎並沒有顯​​示過多。我懷疑它是否與數據倉庫 ETL 流程有關。有沒有其他人見過這樣的事情？

現在，我需要能夠縮小這個數據庫，因為它正在快速且不正確地增長，並且幾乎完全是空的。我知道收縮後的碎片，我會這樣做。

謝謝。

編輯 執行以下查詢以獲取表大小：

SELECT 
   t.NAME AS TableName,
   s.Name AS SchemaName,
   p.rows,
   SUM(a.total_pages) * 8 AS TotalSpaceKB, 
   CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
   SUM(a.used_pages) * 8 AS UsedSpaceKB, 
   CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
   CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN     sys.schemas s ON t.schema_id = s.schema_id
GROUP BY     t.Name, s.Name, p.Rows

顯示表大小，包​​括索引。將其相加返回預期的大約 4GB。我還檢查了索引視圖，它們的大小可以忽略不計。

TotalSpaceMB
3775.16

然而 sp_spaceused 返回這個：

database_size   unallocated space
197479.50 MB    181.38 MB

reserved        data           index_size   unused
201396352 KB    200640656 KB    534448 KB   221248 KB

這向我表明問題出在堆上。但我無法解釋失去的 196GB。

編輯 2 正如 SQLpro 所建議的，它可能是使用空間的 internal_tables。當我們使用服務代理和變更跟踪時，這聽起來很可能。我也喜歡這個建議，因為它可能解釋了為什麼如果服務代理沒有執行/啟用，測試副本可能會更新分配/未使用的空間資訊。

但是，我使用以下查詢來匯總保留和使用的空間，但它只佔大約 1.5GB。

SELECT SUM(ReservedMB) ReservedMB, SUM(UsedSpaceMB) UsedMB
FROM (SELECT s.name, it.name tname,
       it.parent_id,
       SUM(ps.reserved_page_count) AS reserved,
       SUM(ps.used_page_count) AS used,
        CAST(ROUND(((SUM(ps.reserved_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS ReservedMB, 
    CAST(ROUND(((SUM(ps.used_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
    FROM sys.dm_db_partition_stats ps
    INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
           JOIN sys.schemas AS s ON it.schema_id = s.schema_id
    GROUP BY it.parent_id, it.name, s.name)ds

結果

ReservedMB  UsedMB
1318.66     1288.65

解決方案

正如 SQLpro 所提到的，問題確實是由服務代理引起的。我之前對錶大小的所有查詢都沒有找到導致問題的特定內部表。但是，經過一番探勘，我發現了這個查詢：

/* Size of all internal and normal table objects */
 SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id), 
      rows = SUM(CASE
                     WHEN index_id < 2
                     THEN row_count
                     ELSE 0
                 END), 
      reserved_mb = 8 * SUM(reserved_page_count) / 1024, 
      data_mb = 8 * SUM(CASE
                            WHEN 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) / 1024, 
      index_mb = 8 * (SUM(used_page_count) - SUM(CASE
                                                     WHEN 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)) / 1024, 
      unused_mb = 8 * SUM(reserved_page_count - used_page_count) / 1024
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY reserved_mb DESC;

這立即表明了問題。最高紀錄：

name                rows    reserved_mb data_mb index_mb    unused_mb
sys.sysxmitqueue    264598  198097      198082  3           11

感謝所有幫助過的人。我還了解到堆的問題，這對我來說是新的。

在使用 Service Broker 時，我遇到了類似的問題，它使用了許多查詢中沒有看到的內部表來計算數據量。採用 ：

DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + N'EXEC sp_spaceused ''' + s.name + '.' + it.name + ''';'
FROM   sys.internal_tables AS it
      JOIN sys.schemas AS s
         ON it.schema_id = s.schema_id; 
EXEC (@SQL);

查看這些卷

每個分配都有一個 IAM 頁面的連結列表。如果我們忽略分區和 LOB 等以保持討論直截了當，那麼您可以想像每個索引都有一系列 IAM；並且實際數據（無論是聚集索引還是堆）也有一系列 IAM。

一個 IAM 在數據庫文件中映射了 2GB，反映了該分配（堆/索引）擁有哪些範圍。如果分配使用這些 2GB 之外的空間，則當它有一個 IAM 時（每 2GB 它使用數據庫文件中的空間）- IAM 以鍊錶的形式連接。

告訴您表有多大或數據庫有多滿的查詢不會由這些 IAM 執行，這將花費太長時間。他們使用系統表（通過視圖公開），其中包含有關空間使用情況的統計資訊。如您所知，這些可以關閉。但是它們關閉與分配空間的時間無關。當 SQL Server 需要查找可用空間時，它會使用 GAM、SGAM 和 IAM（可能還有 PFS）等頁面。因此，如果您有一個根據 IAM（以及間接地 SGAM）擁有大量範圍的堆（即使那裡沒有任何數據），那麼這些範圍就不能被其他東西使用。

執行 UPDATEUSAGE 不會有幫助，因為該命令只會讀取這些結構（可能只有 IAM）以在系統表中反映分配的大小。即，您執行 UPDATEUSAGE 不是讓您縮小的原因，如果沒有 UPDATEUSAGE，縮小也可以正常工作。

所以，你的堆顯然（如果我的推理是合理的）是你的問題。該怎麼辦？不要堆或重建它們。但是，如果你保持你的堆，問題會回來。

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