沒必要的話就別DBCC SHRINKFILE或DBCC SHRINKDATABASE了吧

近來很忙,因此有點荒廢了BLOG。前幾天剛好有朋友問了一個關於SHRINKDATABASE的問題,讓我興起了LAB的興趣。

朋友遇見的問題是這樣,他有一SQL的磁碟快滿了,因此他將SQL中的一些大資料表先做Page壓縮。壓縮後就釋出一堆空間,因此他想再分一點空間給OS所以就做了SHRINKDATABASE的動作。他表示檔案大約可以縮小幾十GB但跑了一天都跑不完,讓他覺得SHRINKDATABASE真要花那麼久時間?還是有什外在原因造成呢?

聽他這樣說,我想一下自己還真沒對mdf做過壓縮(只做過ldf檔),因此就想了解一下mdf檔在SHRINKFILE下到底在做甚麼。

 

下圖中我建了一資料庫TestDB(復原模式是FULL),建了兩張資料表tb1跟tb2,並各塞了100萬筆資料進去。

 

做完上述動作後,我們看一下磁碟使用的報表,如下圖所示mdf檔還有53%的Free Space。

 

下圖是tb1跟tb2耗費空間,共約62MB。

 

下圖中我們查出tb1資料表在mdf的空間配置,該資料表是從Page編號232 ~ 4242左右,而資料所占Page頁數為3899頁。

 

下圖中我們查出tb2資料表在mdf的空間配置,該資料表是從Page編號4256 ~ 9031左右,而資料所占Page頁數為3900頁。所以mdf空間配置上tb2接在tb1後面

 

模擬朋友的狀況,我先將tb1做Page壓縮

 

上圖的資料表壓縮完後接下來我們開始做mdf的壓縮,壓縮過程中我一併觀察ldf檔的變化及耗時。由下圖中我要將mdf由136MB壓縮至50MB,整個耗時為1分14秒(有點慢),令我驚訝的是ldf檔長大了。原來檔案壓縮也是會紀錄Transaction Log的(這要筆記一下歐)

 

看一下壓縮成果,mdf壓到61.19MB,ldf則長到328MB。

 

ldf長大到底紀錄哪一些東西,因此我看一下Transaction Log。由下圖紅色圈選處可以知道SHRINKFILE是在做資料搬移,因此有一堆Insert跟Delete的動作在這兩張資料表

 

接下來我們看看SHRINKFILE後的tb1空間配置為何呢?由下圖紅色圈選處可以發現tb1的資料頁數為3429頁,但我明明Page壓縮過tb1,當時頁數為1400多頁(忘了截圖)為何SHRINKFILE後tb1又長大了呢?

 

tb2在SHRINKFILE後基本上變化不大。

 

Database在復原模式下為FULL的模式下做SHRINKFILE會導致ldf檔狂長,那在Simple模式下呢? 下圖是我將模式改為Simple後再壓縮一次mdf後的結果,ldf也是會長,但是可以ReUse所以漲幅沒那樣驚人

 

透過上述簡易實驗後,大家須注意兩點 :
  1. SHRINKFILE是會紀錄Transaction Log的,所以要注意放置LDF硬碟之空間。
  2. SHRINKFILE過程中針對有做過資料表壓縮之資料表是會先解開壓縮再做搬移,所以壓縮率會下降。

 

補充 : 這一次LAB後才發現原來資料表壓縮是將原有資料表資料取出壓縮後再放到mdf的其他空間去,而非放回原來的空間(如下圖所示)。

我是ROCK

rockchang@mails.fju.edu.tw