關(guān)于數(shù)據(jù)庫文件的管理問題，我經(jīng)常說，常在江湖混，哪有不挨棍，用的時間長了，基本上都有遇到一些數(shù)據(jù)庫文件管理上的問題，比如說：

1. SQL Server數(shù)據(jù)文件空間滿

2. 日志文件暴漲

3. 文件不能收縮

4. 如何實現(xiàn)文件的自動增長和自動收縮

? ? ? 這篇文章就圍繞這些問題展開，當(dāng)然要想熟練的對數(shù)據(jù)庫空間進(jìn)行管理，需要先了解一下相關(guān)的理論知識。首先，我們先來看一下數(shù)據(jù)文件的空間管理。

數(shù)據(jù)文件空間管理

? ? ? ?每個 SQL Server 數(shù)據(jù)庫至少具有兩個操作系統(tǒng)文件：一個數(shù)據(jù)文件和一個日志文件。 ?數(shù)據(jù)文件包含數(shù)據(jù)和對象，例如表、索引、存儲過程和視圖。 日志文件包含恢復(fù)數(shù)據(jù)庫中的所有事務(wù)所需的信息。 為了便于分配和管理，可以將數(shù)據(jù)文件集合起來，放到文件組中。在SQL Server中數(shù)據(jù)存儲的基本單位稱為頁。每頁是8KB，SQL Server讀取或者是寫入數(shù)據(jù)的最小單位也是頁，那么1MB就有128頁。行不能跨頁（頁的單個行最大數(shù)量是8,060字節(jié)8kb 1024*8)，不包括Text/Image類型的頁數(shù)據(jù)，對于可變長類型的列，如果行超過8060,則從最大長度的列開始，將一個或多個可變長度列移動到ROW_OVERFLOW_DATA分配單元中的頁，在原始頁上維護一個24字節(jié)的指針，如果行的總大小小于8060,就再移回來.執(zhí)行查詢等操作時將延長處理時間，因為這些記錄將同步處理，而不是異步。

如下圖所示：

? ? ? 但還有另一個概念，大家要知道，叫做區(qū)，區(qū)是指8個物理上連續(xù)的頁的集合，如果這8個物理上連續(xù)的頁屬于同一個表，則這種區(qū)稱為統(tǒng)一區(qū)，如果這8個頁分別屬于至少兩個不同的表。則這種區(qū)稱為混合區(qū)。

? ? ?雖然每個頁有8KB，但并不是說這8KB都用來存放具體數(shù)據(jù)，每頁的開頭有一個96字節(jié)的頁頭，用來存儲有關(guān)頁的系統(tǒng)信息，例如：頁碼、頁類型、頁的可用空間以及擁有該頁的對象ID（也就是這個頁是哪個對象在用）。不同類型的數(shù)據(jù)，存放在不同類型的頁里面。如下圖所示，就顯示了數(shù)據(jù)文件中各種頁類型以及它們里面存放的內(nèi)容：

? ? ? ?在這里面，我們用到較多的頁是Data和Text/Image類型，而在一個數(shù)據(jù)文件的開頭則分布很多管理頁面如：GM、SGAM、PFS。SQL Server通過這些頁面知道這個數(shù)據(jù)文件中的哪些頁面已經(jīng)使用，哪些頁面還沒有使用等。

? ? ? 當(dāng)一張表或一個索引需要更多的空間時，SQL Server需要找到能夠用來分配的空間。如果該表或索引整體仍然少于8個頁面，SQL Server必須找到能夠用來分配的混合類型區(qū)構(gòu)成的空間。如果表或索引有8個頁面或更大，SQL Server必須找到一個自由的統(tǒng)一類型的區(qū)。那么SQL Server就需要知道區(qū)已經(jīng)分配出去，哪些區(qū)可以使用，這就要用到全局分配映射頁面和共享全局分配映射頁面，簡稱為：GAM/SGAM。

? ? ?GAM記錄了哪些區(qū)已經(jīng)被分配并用作何種用途。一個GAM頁面在它所覆蓋空間里針對每一個區(qū)都有一個數(shù)據(jù)位。如果為1，則為空閑區(qū)，可以用來分配，如果為0則該區(qū)已經(jīng)被使用。GAM大約能標(biāo)識64000個區(qū)，也就是4G的空間。如果超過4G，則再啟用一個GAM頁來標(biāo)識下一個4G空間。

? ? ? ?SGAM記錄了哪些區(qū)當(dāng)前是混合區(qū)，并且至少有一個未使用的頁面。它也能標(biāo)識64000個區(qū)，大約4G空間。如果bit位標(biāo)識為1，則說明它使用的是混合區(qū)并且至少有一個頁可用來分配 。如果為0，則說明是統(tǒng)一區(qū)，或者是混合區(qū)，但已經(jīng)沒有空閑頁面。

? ? ? ?那么SQL Server可以很方便地查找需要的頁面，如需要一個新的完全沒有使用的區(qū)，那么可以使用任何一個在GAM頁面中對應(yīng)的比特位值為1的區(qū)。如果需要找到一個有著可用空間，如一個或多個自由頁面的混合類型的區(qū)，那么它可以尋找一個對應(yīng)的GAM中的值為0、SGAM中的值為1的區(qū)。如果不存在有可用空間的混合類型的區(qū)，SQL Server會使用GAM頁面來尋找一個全新的區(qū)并將其分配為混合類型的區(qū)，然后使用該區(qū)中的一頁。如果根本沒有自由區(qū)，那么這個文件已經(jīng)滿了。 ??

? ? ? ?SQL Server能夠迅速地鎖定一個文件中的GAM頁面，因為它總是位于任何數(shù)據(jù)庫文件的第三頁上(頁碼為2)。SGAM頁面是在第四頁上(頁碼為3)。下一個GAM頁面出現(xiàn)在第一個GAM頁面(頁碼為2)以后的每511 230個頁面中，并且下一個SGAM頁面出現(xiàn)在第一個SGAM頁面(頁碼為3)以后的每511 230個頁面中。每一個數(shù)據(jù)庫文件的頁碼為0的頁面是文件頭頁面，并且每個文件僅有一頁。頁碼0是頭文件頁，頁碼1是頁面自由空間頁(Page Free Space，PFS)。并且每一個數(shù)據(jù)庫的前八個頁面是固定不變的。

? ? ? ?下面，我們可以通過DBCC Page命令查看某一個數(shù)據(jù)庫的頁面信息，此命令的語法是:

? ? ? ?dbcc page(數(shù)據(jù)庫名稱|數(shù)據(jù)庫ID，文件編號，頁面編號，輸出選項)

輸出選項：0:默認(rèn)值，輸出緩沖區(qū)的標(biāo)題和頁面標(biāo)題；1:輸出緩沖區(qū)的標(biāo)題、頁面標(biāo)題(分別輸出每一行)，以及行偏移量表；2:輸出緩沖區(qū)的標(biāo)題、頁面標(biāo)題(整體輸出頁面)，以及行偏移量表。3：輸出標(biāo)題的同時，顯示列值。

? ? ? ?但需要此命令之前，必須啟用DBCC TRACEON(3604)。

一個完整的頁面包含四個部分BUFFER、PAGE HEADER、DATA、OFFSET TABLE，分別表示緩存、頁面的頭部信息、數(shù)據(jù)和偏移表。

BUFFER:緩存部分，用于標(biāo)識頁在內(nèi)存中的位置。

PAGE HEADER:頁面頭部信息，包括一些重要屬性，如：m_pageid為頁編號；m_headerversion為頁頭格式的版本；m_type為頁的類型，如：1，表明是數(shù)據(jù)頁，2為索引頁，8為GAM頁，9為SGAM頁等；m_typeflagebits:類型標(biāo)識位，基本上用不到；m_level為在B樹結(jié)構(gòu)中的層級，最底層的層級為0；m_flagbits為頁的屬性，如0x200表明頁有checksum檢查；m_prevpage和m_nextpage，在B樹結(jié)構(gòu)的同一層級數(shù)據(jù)頁之間，互相通過m_prevpage和m_nextpage連接起來；m_slotcnt表明當(dāng)前頁中有多少條記錄；m_freecnt表明當(dāng)前頁中還剩余多少 空間，以字節(jié)為單位。m_lsn表明當(dāng)前頁中的所有記錄中，最后一個改變相對應(yīng)的日志記錄號。

DATA部分一般分為若干插槽號(Slot)，如果是數(shù)據(jù)頁或索引頁的話，可以理解為一行記錄，SQLServer通過文件號+頁面號+插槽號用來唯一標(biāo)識表中的每一條記錄。但在GAM頁中我們可以把Slot 0理解為GAM頁的保留頁，共計94個字節(jié)。

頁的末尾是行偏移表，對于頁中的每一行，每個行偏移表都包含一個條目。每個條目記錄對應(yīng)行的第一個字節(jié)與頁首的距離。行偏移表中的條目的順序與頁中行的順序相反。如下圖所示：

? ? ?我們可以看到第一個數(shù)據(jù)行，也就是上圖中的最后一個記錄，偏移量是96，這是因為正常好前面的是96個字節(jié)的頭部，接下來正好是第一條記錄，其他記錄的分析，依次類推。

轉(zhuǎn)載于:https://www.cnblogs.com/lenther2002/p/4494974.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server 数据库文件管理的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。