檔案自動成長和自動收縮sql server

1.4  檔案自動成長和自動收縮

SQL Server允許使用者佈建資料庫初始值、最大值，可以自動成長或者自動收縮。通過這些設定，可以防止資料庫空間問題而導致的應用程式修改失敗或者SQL Server把硬碟空間耗盡之類的事情發生。一般來講，如果資料庫不是很繁忙，預設的設定（開啟自動成長）能夠滿足大部分的需求。但是資料檔案和記錄檔增長本身是一件耗費系統資源和影響效能的工作。所以如果完全依賴SQL Server自動完成，可能會導致系統效能不夠穩定。一個管理得比較精細的系統，應該預先考慮到可能的空間使用需求，提前規劃並引導資料的流向。盡量避免空間用盡而使得SQL Server不得不自動成長的現象發生。同時也要確保每一次自動成長都能夠在可接受的時間內完成，及時滿足用戶端應用的需求。

那麼怎麼才能達到這樣的目的呢？在談論最佳配置之前，首先要討論一下SQL Server資料檔案和記錄檔空間申請的一些特點。還是以下面這個資料庫（1-31所示）為例。它有3個資料檔案（假設它們屬於同一個檔案組）和兩個記錄檔（見表1-7）：

表1-7  插入資料前的檔案大小

假設現在有個用戶端要插入40 MB的資料，20 MB的日誌記錄，SQL Server會怎樣往這些檔案裡寫呢？SQL Server對於資料和日誌有不同的處理方法。

資料檔案

SQL Server會按照同一個檔案組裡面的所有檔案現有空閑空間的大小，按這個比例把新的資料分布到所有有空間的資料檔案裡。如果某個檔案已經寫滿了，SQL Server就不再繼續往這個檔案裡寫，而是寫到其他有空間的檔案裡面。

在上面的例子裡，因為3個檔案空閑空間是200:100:100，40 MB的資料就按照20 MB:10 MB:10 MB的比例寫入了這3個檔案。

圖1-31  範例資料庫的資料檔案和記錄檔

 

記錄檔

SQL Server對於日誌記錄是按照嚴格的順序寫入的。所以雖然這裡有兩個記錄檔，SQL Server還是在一個時間點唯寫其中的一個。只有這個檔案寫滿了，SQL Server才會寫入另外一個。

在我們的範例資料庫裡，20 MB的日誌記錄都會寫入MyDB_log1。

有時候我們會加入多個資料庫檔案，並把它們放在不同的硬碟上，以達到分散I/O負載的目的。從上面的處理方式我們可以看到，如果想要達到這個目的，對於資料檔案，就必須保證同一個檔案組裡的所有資料檔案都有基本一樣大小的空閑空間。（不是這些檔案一樣大就可以的。）如果某個硬碟上的資料檔案已經被寫滿了，SQL Server就不會再往這個硬碟上寫了。如果空閑空間相對比較少，SQL Server寫的數目也會相對減少。

對於記錄檔，由於SQL Server在同一個時間唯寫一個檔案，所以加入多個記錄檔對效能基本不會有什麼協助。

如果檔案全部都寫滿了，SQL Server會怎麼處理呢？在這裡資料檔案和記錄檔也會稍有不同。對於資料檔案，SQL Server會選取其中一個檔案（可能是任意一個）做自動成長，而不是讓每一個資料檔案都做自動成長。所有後面的資料都寫入這個做了自動成長的檔案裡，直到這個檔案再次寫滿，SQL Server要做下一次自動成長為止。換句話說，依靠自動成長，只能看到一個檔案增長，很難享受到I/OServer Load Balancer的效果。

對於記錄檔，SQL Server自動成長當前的記錄檔，以保證日誌記錄的連續性。

當某個操作觸發了檔案自動成長時，SQL Server會讓那個操作等待。直到檔案自動成長結束了，原先的那個操作才能繼續進行。如果自增長用了很長時間，原先的操作會等不及就逾時取消了（一般預設的閾值是15秒），不但這個操作會復原，檔案自動成長也會被取消。也就是說，這一次檔案沒有得到任何增大。最壞的情況是，在一個時間點，有很多操作需要申請新的空間，可是誰都沒能夠等檔案自動成長完就逾時。這時體現在終端使用者的感覺，就是任何修改操作都不能被提交，全部逾時。直到有一個串連能夠等足夠久，讓SQL Server把這個自動成長做完。做完以後，其他本來逾時的操作又忽然都能恢複正常。

為什麼一個自動成長可能會花比較長的時間呢？這基本上都是由於每次需要增長的空間太大造成的。資料檔案是按照8 KB為單位儲存的。所以做資料檔案自增長的時候，SQL Server也要對這些新增加的部分進行格式化。如果一次要增長很大的空間，比如，上GB或者幾十GB，這個格式化的過程就會很耗時。SQL Server 2005以後的版本採用了延遲寫技術。只要增長的新空間已經分配好，這次自動成長就算大功告成。SQL Server會用一個背景線程把剩餘的格式化做完。這樣就大大縮短了一次自增長的時間。前端不再容易遇到逾時失敗。

還有一種極端，就是每次自動成長值太小。SQL Server要做好幾次自增長才能滿足操作需求。同樣的大小，一次一步到位花的時間比分好幾次增長要少許多。所以自動成長值也不能太小。

總之，設定資料庫自增長要注意以下幾點。

（1）要設定成按固定大小增長，而不能按比例。這樣就能避免一次增長太多或者太少所帶來的不必要的麻煩。建議對比較小的資料庫，設定一次增長50 MB到100 MB。對大的資料庫，設定一次增長100 MB到200 MB。

（2）要定期監測各個資料檔案的使用方式，盡量保證每個檔案剩餘的空間一樣大，或者是期望的比例。

（3）設定檔案最大值，以免SQL Server檔案自增長用盡磁碟空間，影響作業系統。

（4）發生自增長後，要及時檢查新的資料檔案空間分配情況。避免SQL Server總是往個別檔案寫資料。

除了自動成長，資料庫還有一個自動收縮的功能。如果設定了這個功能，SQL Server每隔半個小時就會檢查檔案使用方式。如果空閑空間大於25%，SQL Server就會自動運行DBCC SHRINKFILE的動作。所以這個功能能夠防止資料庫申請過多的空間而不使用。對一個硬碟空間很緊張的系統，這個設定無疑是有協助的。但是從資料庫自身的健康和效能考慮，這個設定並不建議多用。這是因為：

（1）SQL Server只有在空間用盡的情況下才會做自動成長。如果沒有找出自增長的原因，從而從根本上避免空間用盡，雖然能夠暫時用DBCC SHRINKFILE功能收縮檔案大小，但是下次資料庫還是有可能長大。收縮資料庫只是一個治標不治本的方法。

（2）資料檔案收縮會給檔案帶來更多的片段。

（3）不管是資料庫收縮，還是增長，對SQL Server來講都是件浪費資源的事情。在負載比較重的系統裡，對效能的影響尤其大。它們是應盡量避免而不是鼓勵的操作。

因此，對於一個比較繁忙的資料庫，推薦的設定是開啟資料庫自動成長選項，以防資料庫空間用盡導致應用程式失敗，但是要嚴格避免自動成長的發生。同時，盡量不要使用自動收縮功能。

 

 

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

size 就是初始大小，maxsize 是最大大小，filegrouth 是增量(可能是大小，可能是百分比)
當檔案大小增長到一定值就會 按增量 增長。