Sql-Server

如何釋放表的未使用空間

  • September 28, 2017

這個問題被問了幾十次，令我驚訝的是，這麼簡單的要求竟然如此困難。然而我無法解決這個問題。

我使用 SQL Server 2014 Express 版本，數據庫大小限制為 10GB（不是文件組大小，數據庫大小）。

我抓取了新聞，並將 HTML 插入到表格中。表的架構是：

Id bigint identity(1, 1) primary key,
Url varchar(250) not null,
OriginalHtml nvarchar(max),
...

數據庫用完了，我收到了insufficient disk space

當然，縮小數據庫和文件組並沒有幫助。DBCC SHRINKDATABASE沒有幫助。所以我寫了一個簡單的應用程序來讀取每條記錄，去掉一些不需要的部分，OriginalHtml比如頭部、旁邊和頁腳，只保留主體，我現在在通過頂部表格獲取磁碟使用情況報告時看到這個圖像：

在此處輸入圖像描述

據我了解這張圖片，未使用的空間現在佔總大小的 50%。也就是說，現在我有 5GB 的未使用空間。但我無法收回它。重建索引沒有幫助。該truncateonly選項無濟於事，因為據我了解，不會刪除任何記錄，只會減少每條記錄的大小。

我被困在這一點上。請幫忙，我該怎麼辦？

聚集索引在列上Id。

這是結果EXECUTE sys.sp_spaceused @objname = N'dbo.Articles', @updateusage = 'true';

name        rows     reserved     data        index_size   unused
----------- -------- ------------ ----------- ------------ -----------
Articles    112258   8079784 KB   5199840 KB  13360 KB     2866584 KB

在所有條件相同的情況下，壓縮大對象 (LOB) 列應該足夠了OriginalHTML。您沒有在問題中指定聚集索引名稱，因此：

ALTER INDEX ALL
ON dbo.Articles
REORGANIZE 
WITH (LOB_COMPACTION = ON);

看ALTER INDEX (Transact-SQL)

如果您有聚集索引名稱（不僅僅是聚集列），請將ALL上面的內容替換為該名稱。

該LOB_COMPACTION選項預設為ON，但明確表示沒有害處。您可能需要REORGANIZE重複執行以完成回收所有未使用的空間。

不幸的是，LOB 數據的組織方式和 LOB 壓縮的實現方式意味著該方法可能並不總是能夠回收所有未使用的空間，無論您執行多少次。它也可能非常緩慢。

您也可以嘗試相關問答中的方法釋放未使用的空間 SQL Server 表

如果由於某種原因，上述方法對您不起作用，請將數據導出到文件，截斷表，然後重新載入。有幾種方法可以實現這一點，例如bcp 實用程序。

例子

下面創建一個包含 10,000 行寬的表：

CREATE TABLE dbo.Test 
(
   c1 bigint IDENTITY NOT NULL, 
   c2 nvarchar(max) NOT NULL,

   CONSTRAINT PK_dbo_Test
       PRIMARY KEY CLUSTERED (c1)
);

-- Load 10,000 wide rows
INSERT dbo.Test WITH (TABLOCKX)
   (c2)
SELECT TOP (10000)
   REPLICATE(CONVERT(nvarchar(max), 'X'), 50000)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2;

我們可以使用sys.dm_db_index_physical_statsDMV 查看空間使用情況：

SELECT
   DDIPS.index_id,
   DDIPS.partition_number,
   DDIPS.index_type_desc,
   DDIPS.index_depth,
   DDIPS.index_level,
   DDIPS.page_count,
   DDIPS.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
   DB_ID(),
   OBJECT_ID(N'dbo.Test', N'U'),
   1,
   NULL,
   'DETAILED'
) AS DDIPS
WHERE 
   DDIPS.alloc_unit_type_desc = N'LOB_DATA';

DMV 輸出

我們現在將 LOB 內容更新為更小的大小（但仍需要行外儲存）：

-- Change LOB data to a smaller value (that will not move in-row)
UPDATE dbo.Test WITH (TABLOCKX)
SET c2 = REPLICATE(CONVERT(nvarchar(max), 'Y'), 5000);

DMV 輸出

請注意，一些空間已被回收，但剩餘的頁面比原來的要少得多。

我們可以使用以下方法壓縮 LOB 空間：

ALTER INDEX PK_dbo_Test ON dbo.Test 
REORGANIZE 
WITH (LOB_COMPACTION = ON);

DMV 輸出

這會導致一些壓縮和空間節省，但並不完美。再次執行壓縮可能會也可能不會改善這種情況。在我的測試中，無論我重新執行多少次，它都沒有。

導出、截斷、重新載入

完全從 Management Studio 執行此操作的一種方法是使用xp_cmdshell將表數據導出到文件。如果xp_cmdshell目前未啟用，以下將執行此操作：

-- Enable xp_cmdshell if necessary
EXECUTE sys.sp_configure
   @configname = 'show advanced options',
   @configvalue = 1;

RECONFIGURE;

EXECUTE sys.sp_configure
   @configname = 'xp_cmdshell',
   @configvalue = 1;

RECONFIGURE;

現在我們可以執行導出：

-- Export table
EXECUTE sys.xp_cmdshell
   'bcp Sandpit.dbo.Test out c:\temp\Test.bcp -n -S .\SQL2017 -T';

請注意，您將需要更改路徑和-S伺服器名稱，並可能提供登錄憑據。

我們如何截斷表，並使用以下方法重新載入它BULK INSERT：

-- Truncate
TRUNCATE TABLE dbo.Test;

-- Switch to BULK_LOGGED recovery model if currently set to FULL
-- Bulk load
BULK INSERT dbo.Test
FROM 'c:\temp\Test.bcp' 
WITH 
(
   DATAFILETYPE = 'widenative', 
   ORDER (c1), 
   TABLOCK,
   KEEPIDENTITY
);

最後一步是重置身份種子：

-- Check and reseed identity
DBCC CHECKIDENT('dbo.Test', RESEED);

此操作序列通常比 LOB 壓縮更快，並且應該始終產生最佳結果：

DMV 輸出

由於長期存在的錯誤，上面的效率不如它可能的效率：帶有 IDENTITY 列的 BULK INSERT 創建帶有 SORT 的查詢計劃。那裡列出的解決方法是有效的，但如果表非常大，我只會打擾它。

不要忘記刪除用於保存導出數據的臨時文件。

您當然可以自由使用對您來說最方便的任何批量導出/導入方法。不需要使用xp_cmdshell或bcp。

附加條款：

  • FILLFACTOR僅適用於索引頁。它不影響行外 LOB 儲存（不儲存在索引頁上）。
  • 行和頁面壓縮不適用於行外儲存。
  • 作為替代方案，您可以使用SQL Server 2016 中提供的COMPRESS和函式顯式壓縮和解壓縮數據。DECOMPRESS

對於那些使用 SQL Server 2014（這裡就是這種情況）或更早版本（低至 SQL Server 2005）來獲得由COMPRESS和DECOMPRESS內置函式提供的相同壓縮功能的使用者，一個選項是使用 SQLCLR。Solomon Rutzky編寫的免費版SQL#中提供了執行此操作的預建構函式。Util_GZip和Util_GUnzip函式應分別等效於和。而且，任何使用 SQL Server 2012 或更高版本的人都應確保執行 SQL Server 的伺服器已更新為 .NET Framework 4.5 或更高版本，以便使用大大改進的壓縮算法。COMPRESS``DECOMPRESS

如果您可以升級到 SQL Server Express 2016 SP1 或更高版本，則可以通過使用DATA COMPRESSION節省大量空間。

您可能還有其他事情正在使您的數據庫膨脹，但是，正如 Dan Guzman 的評論所建議的，您應該檢查所有索引的填充因子。

除了 0（零）或 100 以外的任何值都意味著，在創建（或重建）索引時，SQL Server 僅將每個頁面填充到填充因子的百分比。因此，例如，如果您的填充因子為 50，則在索引創建/重建期間只有 50% 的頁面會被填充，這基本上會使實際保存數據所需的空間量增加一倍。

從為 SQL Server 數據庫中的索引查找填充因子的文章中提取查詢

如果要查找 SQL Server 數據庫中填充因子不是 0 或 100 的所有使用者表的所有索引：

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, o.type_desc
, i.name AS Index_Name
, i.type_desc AS Index_Type
, i.fill_factor
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
AND i.fill_factor not in (0, 100)
ORDER BY i.fill_factor DESC, o.name

可以在以下位置找到與填充因子相關的其他有價值的資訊

關於填充因子的 5 件事

閃電戰結果：填充係數 (%)

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

相關問答

Sql-Server

縮小日誌文件不會減小大小

  • May 18, 2021
檢視問答
Sql-Server

DBCC Shrinkfile 在完全備份恢復之後但在差異恢復之前

  • January 22, 2022
檢視問答
Sql-Server

DBCC SHIRNKFILE(Data_File, 0) 命令不應該將數據文件縮小到最後分配的範圍嗎？

  • August 19, 2020
檢視問答
Sql-Server

收縮數據庫和文件有什麼區別？

  • February 5, 2020
檢視問答
Sql-Server

縮小數據庫上的日誌文件（通過 SSMS UI 或發出 DBCC ShrinkFile 命令）會出現哪些實際問題？

  • December 12, 2019
檢視問答
Sql-Server

SHRINKFILE 失敗 - 為什麼增加文件大小可以解決它？

  • January 25, 2019
檢視問答