從 varbinary(max) 清空數據後縮小數據庫的最佳方法？

我們有一個數據庫，其中有大量數據儲存在**varbinary(max)**類型的欄位中。在某些時候，我們可以清除大多數行的數據，但不是全部。我們的計劃是使該欄位可以為空，並在不再需要時簡單地將數據清空。一旦我們這樣做了，我們就想減小數據庫的大小。實現這一目標的最佳方法是什麼？

如果目前設置沒有回收空間的好方法，我的一個想法是將該數據欄位移動到只有兩列的單獨表中：主表的鍵和數據欄位。然後我們可以在不再需要這些行時簡單地刪除它們。（然後進行某種收縮。）但是，與簡單地使現有欄位可為空相比，這將是一個更困難的更改。

注意：我實際上並不太關心使數據庫文件更小，但我確實關心新釋放的空間變得可重用。

超過 90% 的數據庫大小是這一欄位。我已經3TB了。

在我看來，只是將列更新為NULL將釋放頁面以供重用。這是一個非常 Scottish® 的展示，以慶祝它幾乎是美國東部標準時間下午 5 點。

USE tempdb;

DROP TABLE IF EXISTS dbo.RobertBurns;

CREATE TABLE dbo.RobertBurns
(
   Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
   Scotch VARCHAR(50),
   HaggisAddress VARBINARY(MAX)
);

DECLARE @AddressToAVarbinaryHaggis VARBINARY(MAX); 
DECLARE @AddressToAHaggis NVARCHAR(MAX) = N'
Good luck to you and your honest, plump face,
Great chieftain of the pudding race!
Above them all you take your place,
       gut, stomach-lining, or intestine,
You''re well worth a grace
       as long as my arm.

The overloaded serving tray there you fill,
Your buttocks shaped like a distant hilltop,
Your wooden skewer could be used to fix a mill
        if need be,
While through your pores your juices drip
        like liquid gold.

His knife see the serving-man clean,
And then cut you up with great skill,
Making a trench in your bright, gushing guts
       To form a ditch,
And then, 0h! What a glorious sight!
       Warm, steaming, and rich!

Then, spoonful after spoonful, they eagerly eat,
The devil will get the last bit, on they go,
Until all their well-stretched stomachs, by-and-by,
       are bent like drums,
Then the head of the family, about to burst,
       murmurs “Thank the Lord".

Is there a pretentious soul who, over his French ragout,
Or Italian cuisine that would make a pig sick,
Or French stew that would make that same pig ill
       with complete and utter disgust,
Looks down with a sneering, scornful attitude,
       on such a meal? (as Haggis)

Poor devil! See him over his trash!
As feeble as a withered bullrush,
His skinny leg no thicker than a thin rope,
       His fist the size of a nut,
Through a river or field to travel,
       Completely unfit!

But look at the healthy, Haggis-fed person!
The trembling earth respects him as a man!
Put a knife in his fist,
       He''ll make it work!
And legs, and arms, and heads will come off,
       Like the tops of thistle.

You Powers who look after mankind,
And dish out his bill of fare,
Old Scotland wants no watery, wimpy stuff
       That splashes about in little wooden bowls!
But, if You will grant her a grateful prayer,
       Give her a Haggis!';


INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 1000 
CASE WHEN x.c % 15 = 0 THEN 'Laphroaig'
    WHEN x.c % 5 = 0 THEN 'Lagavulin'
    WHEN x.c % 3 = 0 THEN 'Port Ellen'
    ELSE 'Ardbeg'
END AS Scotch, 
CONVERT(VARBINARY(MAX), REPLICATE(@AddressToAHaggis, x.c % 20 + 1))
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS c
FROM sys.messages AS m
) AS x;

CREATE INDEX ix_novarbinary ON  dbo.RobertBurns (Scotch, Id);
CREATE INDEX ix_yesvarbinary ON dbo.RobertBurns (Scotch, Id) INCLUDE (HaggisAddress);

插入行後，讓我們檢查索引頁。

SELECT   OBJECT_NAME(i.object_id) AS table_name,
        i.name AS index_name,
        MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
  AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'RobertBurns'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

插入後，我得到了這個。實際頁面可能因您而異。

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5587
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5581

讓我們NULL出來一些行！

UPDATE rb
   SET rb.HaggisAddress = NULL
FROM dbo.RobertBurns AS rb
WHERE rb.Id % 15 = 0;

並返回我們的頁面：

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5300
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5273

所以頁數減少了。噓！對於接觸我們VARBINARY數據的兩個索引，它們失去了一堆頁面。這意味著它們將重新流通以供其他對象使用。由於我在 tempdb 中，它們可能很快就會被這裡發生的所有垃圾內容吞噬。

現在讓我們放回一些數據：

INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 10 rb.Scotch, rb.HaggisAddress
FROM dbo.RobertBurns AS rb;

並重新簽入：

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5330
RobertBurns ix_novarbinary                  11
RobertBurns ix_yesvarbinary                 5305

頁數略有增加。

因此，看起來您不必做任何太瘋狂的事情，甚至無需縮小數據庫即可重用空間。我認為您將刪除列的行為DBCC CLEANTABLE與您實際正在做的事情混為一談。

希望這可以幫助！

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