沒有叢集索引的資料表，以 Delete 刪除記錄後不會釋放空間

–故意塞 1 千萬筆記錄到 Northwind DB 的資料表，但該資料表沒有建立 Clustered Index
DELETE t

雖然刪除所有記錄，之後查詢空間使用
exec sp_spaceused

dbcc showcontig(t)

結果如下：

DBCC SHOWCONTIG scanning ‘t’ table…
Table: ‘t’ (373576369); index ID: 0, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 6248
– Extents Scanned…………………………: 783
– Extent Switches…………………………: 782
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 99.74% [781:783]
– Extent Scan Fragmentation ……………….: 0.26%
– Avg. Bytes Free per Page…………………: 7954.6
– Avg. Page Density (full)…………………: 1.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

可以看到資料表 t 依然占用非常多的資料頁，但每一頁都幾近空的，可是仍有管理資訊。

能透過 dbcc shrinkfile 釋放頁面

dbcc shrinkfile(Northwind_Data,10)

Database 那 11G 的空間是因為沒有縮 Log(Delete 另一個不好的地方)，但資料頁已經縮下來了。

若可以的話，清空資料表用 truncate 是比較好的

碰到一個國內的流程產品，其部分系統資料表有建 PK，但沒有 Clustered Index。使用一陣後，查詢 sysindexes 系統資料表/檢視，可以發現保留空間是實際資料量的數十倍，但 DBCC Shrinkdatabase 無法解決問題，這造成維護、備份的困擾，建了Clustered Index 就解決問題了。

另外，SQL 2005 後可以透過 sys.dm_exec_requests 查詢執行語法進度，例如 DBCC Shrinkdatabase

廣告