你所不知道的SQL Server資料庫啟動過程（使用者資料庫載入過程的疑難雜症）

標籤：style   blog   http   io   color   ar   os   使用   sp   

 前言

本篇主要是上一篇文章的補充篇，上一篇我們介紹了SQL Server服務啟動過程所遇到的一些問題和解決方案，可點擊查看，我們此篇主要介紹的是SQL Server啟動過程中關於使用者資料庫載入的流程，並且根據載入過程中所遇到的一系列問題提供解決方案。

其實SQL Server作為微軟的一款優秀RDBMS，它啟動的過程中，本身所帶的那些系統庫發生問題的情況相對還是很少的，我們在平常使用中，出問題的大部分集中於我們自己建立的使用者資料庫。

而且，相對於側重面而言，其實我們更關注的是我們自己建立的使用者資料庫，假如系統資料庫出現問題，甚至執行個體出現問題，最壞的情況我們重搭環境，但是如果我們應用的使用者資料庫壞掉了，那可不是重搭環境就能解決的。這牽扯到公司利益問題，問題嚴重性不言而喻！

閑言少敘，我們速度進入本篇的正題。

 

上一篇我們介紹了SQL Server執行個體啟動的過程，並且分析了其詳細的過程，而在這一流程中，有一個步驟非常關鍵，就是載入恢複使用者資料庫的過程，我們來截取這段日誌資訊：

 

上面是一個正常啟動各個使用者庫的流程，SQL Server會採用多線程的進行資料庫啟動，並且在這個過程中進行一致性校正，確保啟動的資料庫能夠正常使用。

而這過程中會發生很多問題，在分析問題之前，我先要介紹SQL Server資料庫的幾個常見狀態：

 RECOVERING（恢複中）：

這個狀態表示資料在啟動完成後，正在發生恢複，也就是上面日誌中的 Recovery過程，和其它的關係型資料庫一樣，SQL Server對所有的資料庫行為都是先寫交易記錄，然後在修改記憶體中的資料，然後通過背景一個進程在適當的時候進行寫入硬碟（Lazy write），所以在資料庫運行過程中，磁碟中的資料並不是最新的，如果這個時候關閉了，在下一次啟動過程中SQL Server就要根據交易記錄中的記錄，將磁碟中的舊的資料改寫，改寫過程為：

1、重做redo

2、復原和撤銷 undo/rollback

上面的目的就是為了保證資料庫一致性。

如果上面的流程發生了問題，就會進去到下面這個狀態：

RECOVERY PENDING（掛起還原）：

這個過程就是將恢複資料的過程掛起，掛起的原因基本就是不能正常開啟所用的資料庫檔案。這裡先記住這個狀態就行，我在後面的內容會再現這個問題，以及給出解決方案。

如果能找到檔案或者能開啟檔案，但是檔案有問題，機會出現下面這個狀態：

SUSPECT（質疑）：

這個狀態，我相信很多使用者如果在玩資料庫久了的時候，會偶爾遇到，相對於其它狀態，這個狀態是出現最高的。

原因很簡單：資料庫檔案壞掉了。

當經曆了上面的這個幾個狀態都不出現問題，上面的這幾個狀態下，資料庫都是不能使用的，會進入到下面這個狀態：

ONLINE（線上）：

這個狀態應該是最期待的了，資料庫線上，正常使用，預設都是正常的線上狀態。

 

當然，除了上面幾個資料庫自己形成的資料庫狀態，在我們管理員處理資料庫的時候也會更改狀態，這裡我們順便提一下：

OFFLINE(離線)：有線上狀態就有離線狀態，很簡單，讓資料庫離線，使用者不能使用

RESTORING（還原中）：這個狀態很簡單，管理員正在還原該資料庫，不解釋

EMERGENCY（緊急）：這個狀態也是管理員用的，就是說明資料庫有問題了，它正在盡量解決

 

以上幾個狀態中，發生在啟動過程中，並且會發生問題就是上面的RECOVERY PENDING（掛起還原）、SUSPECT（質疑）、RECOVERING（恢複中）：

我們依次來看：

RECOVERY PENDING（掛起還原）：

出現這個狀態通常的原因是資料庫檔案找不到，或者檔案找到許可權訪問不到，我們來看該問題報錯資訊：

在資料庫中儲存方式中，分為主檔案組和輔助檔案組和記錄檔，為了展示方便我們特意建立了個測試庫，來重現該部分問題：

 

 

<1>主檔案組問題

當不能訪問主檔案組檔案的時候，也就是上面的CnblogsTestDB.mdf檔案，會報如下錯誤：

我們先來看資料庫：

在執行個體啟動的過程，恰巧有一個庫顯示了上面我們提到的一個狀態：RECOVERING（恢複中），我順便把圖給了，當然出現這個情況很正常，有時候重新整理一下就正常，其它使用者庫沒有顯示是因為庫太小，恢復太短，我們捕捉不到。

我們來看，上面我們建立的測試庫CnblogsTestDB已經不能訪問了，我們來看一下Error中的錯誤資訊：

錯誤資訊很明顯，說這個該檔案不能訪問，並且確切的說出了這個為作業系統錯誤，那我們看作業系統的錯誤記錄：

可以看到在Windows系統日誌中也能看到該部分錯誤資訊。

解決方案：

此問題的解決方案還是很簡單的，一般主要是因為許可權問題，只需要將資料庫管理員賬戶組，提權到可讀寫權限就可以，然後重啟服務:

 

上面的情況是找到資料庫檔案，但是不能開啟資料庫檔案，當然還有可能是直接找不到資料庫檔案，系統會報出如下錯誤：

會給出17204錯誤，報找不到檔案錯誤

解決方案：

a、如果能找到資料檔案最好了，拷貝到錯誤制定的路徑下既可以，然後重啟執行個體

b、不能找到檔案了，那就得只能刪除該庫，重新建立同名庫，從備份檔案中還原

一般上述問題發生在實體儲存體出現了故障，當然不排除某些軟體操作，比如殺毒軟體、還有人為誤刪等原因。如果沒有備份，這可能是一個很大的遭難，基本可以確定的完全還原的可能性不高！所以記住：備份資料庫的重要性！

 

<2>輔助檔案組問題

上面的出現問題的檔案為資料庫的主檔案組，當我們資料庫在承載到一定資料量的情況下，我麼採取多個輔助檔案組來容納資料，下面我們來看一下輔助檔案組的問題：

同樣的提示的輔助檔案組不能正常開啟，或者找不到相關的輔助檔案組，遇到這樣的問題我們怎麼解決呢?

其實SQL Server資料庫輔助檔案儲存體的主要為資料庫的資料內容資訊，關於本庫的一些架構資訊是放在主（primary）檔案組中，所以我們可以先這樣

解決方案：

a、我們將打不開或者不能訪問的資料庫檔案（輔助檔案）設定成離線，然後先將能夠正常的資料檔案上線，確保除了損壞的那部分檔案的其它庫資訊能正常訪問，我們通過以下代碼更改：

ALTER DATABASE CnblogsTestDB MODIFY FILE(NAME=CnblogsTestDB2,OFFLINE)
GO
ALTER DATABASE CnblogsTestDB set ONLINE
GO

這樣，我們重新整理下資料庫，既可以正常訪問正確的資料資訊：

當我們處於生產環境中，生產庫不能正常啟動的時候，此刻的火燒眉毛的時刻，採取上面的方法先確保一部分資料能正常訪問也不失為一種緩議之計。

下面的步驟就是找到該輔助檔案，並且確保有正常的許可權訪問，更重要的是找到的輔助檔案不能是損壞的，然後拷貝至錯誤檔案中給出的路徑，然後重啟執行個體，上線該庫。

b、當然大部分情況下，我們找不到該檔案，或者這個檔案已經損壞，那就得採取第二種方案，通過備份還原，根據以往的經驗，建議採取的措施是：

先將能訪問的資料庫做一次備份，然後通過檔案組恢複的方式，恢複上面出問題的檔案組。

 

<3>記錄檔組

其實從市面上的所有資料庫而言，其本身所有的機制都是通過先寫日誌，然後通過一個進程後寫入（lazy write）方式寫入到磁碟，這種方式是為了避免IO的阻塞，因為我們都知道磁碟IO這個問題一直是所有檔案讀寫的最大瓶頸。

所以，記錄檔是資料庫不可分割的一部分。當資料庫在啟動的過程，會通過日誌中的記錄做一次資料的一致性校正，文章的開端有介紹。

所以說，如果記錄檔不能訪問，或者說出問題，那我們的SQL Server資料庫會出現什麼問題呢？

我們先來看資料庫模式為簡單（SIMPLE）模式的，我將咱們的測試庫設定成簡單模式：

USE CnblogsTestDBGOALTER DATABASE [CnblogsTestDB] SET RECOVERY SIMPLE WITH NO_WAITGO

然後我們停掉執行個體，然後刪除掉該庫的記錄檔，然後重新啟動

可以看到處於簡單模式下，如果記錄檔出現錯誤，在啟動的過程是不會發生任何問題的，這裡的原因我們在啟動Error記錄檔中能找到答案：

經過上面的日誌分析，我們可以看到，當資料庫處於簡單模式下，資料庫在啟動的過程中，如果發現任何與日誌相關的資訊，則會重新建立一份記錄檔，保證資料庫的正常訪問。

如果這樣那我們資料庫的完整性怎麼保證呢，是這樣，如果資料庫處於簡單模式，在我們資料庫關閉的時候，系統會先將該提交的所有事務都寫入到磁碟中去，所有該復原的就撤銷。

上面能正常建立資料庫記錄檔的前提條件有兩條：1、資料庫為簡單模式；2、資料庫正常關閉，保證事務都已正常寫入磁碟

 

下面我們在看看如果復原模式為“完整”模式下的，資料庫上次沒有正常的情況，SQL Server資料庫是如何處理的，

我們先將資料庫改成完整復原模式，停掉執行個體，然後刪除日誌，然後啟動

然後我們啟動，可以看到這個時候，資料庫不能正常訪問的，該錯誤的Error的日誌資訊為：

windows平台下也為我們記錄了該錯誤的日誌資訊：

其實出現上面的錯誤，很正常，因為有些資料庫的事務性操作已經記錄到交易記錄中，還未寫入磁碟資料頁中，這時候發生了宕機，或者非正常關閉，這個對SQL Server資料庫是能應付的，但是，而在啟動的過程找不到相關的交易記錄盡心復原和寫入操作，所以該庫的資料時非一致性的，所以SQL Server是不讓我們使用該庫，出現此種錯誤，我們的解決方式有如下幾種：

解決方案：

a、如果有備份，最好最快的方式就是恢複Database Backup或者找到了該記錄檔拷貝到錯誤路徑下（推薦）

b、如果沒有備份，我們只能通過使用CHECKDB命令修複資料庫（不推薦）

上述解決方案中CHECKDB命令，是一種萬不得已的方式，而且，我可以明確的告訴你這命令使用的時候會可能造成資料丟失，並且在大資料庫中，運行周期很長！

當然在萬不得已的情況下，我們還的採取，過程如下：

我們先將資料庫設定成EMERGENCY（緊急）模式，並且為單使用者（SINGLE_USER）模式

 

USE CnblogsTestDBGOALTER DATABASE CnblogsTestDB SET EMERGENCYGOALTER DATABASE CnblogsTestDB SET SINGLE_USERGO

 

經過我們上面的設定，將庫設定成了“緊急”模式，並且只為單使用者方式訪問，便於我們進行資料修複

然後我們執行CHECKDB命令，進行資料庫的修複

DBCC CHECKDB(CnblogsTestDB,REPAIR_ALLOW_DATA_LOSS)GO

經過該命令的修複，資料庫會為系統建立一個日誌，但是不能保證事務的一致性，也就是說會因此而遺失資料，所以非常不推薦的一種方式！

並且，在這過程中，如果是大資料庫的話，該修複過程會很漫長，當然我不能給出一個漫長參考值，因為這過程還有會出現其它的錯誤需要修複。

所以酌情考量。

當然，在恢複完成之後，不要忘記將資料庫改回多使用者模式

USE [master]GOALTER DATABASE [CnblogsTestDB] SET  MULTI_USER WITH ROLLBACK IMMEDIATEGO

至此，這個有問題的庫就能夠正常訪問了。

 

----------------------------------------------------------霸氣的分割線-----------------------------------------------------------------------

在經曆了上面的檔案層級錯誤後，在資料庫啟動的過程，還經常出現的是資料頁層級的錯誤，相對於上面的檔案錯誤層級，在資料頁中造成的錯誤粒度更小，並且基本不會反映到資料庫層級，也就是說在出現資料頁層級的錯誤時候，該資料時可以正常訪問的，只是在訪問有錯誤的資料頁的時候才會報錯，在我們遇到這種錯誤的時候該如何解決呢？

下面我們依次來分析，首先我們來製作一個經典的824錯誤，以下部分內容牽扯到資料庫部分基礎，限於篇幅，我們不做詳細介紹：

<1>首先我們在我們的測試庫中建立一個表，我們將該表建立成一行為一個資料頁的方式，也就是說一行資料庫在資料庫中就能承載一個資料頁

USE CnblogsTestDBGOCREATE TABLE [dbo].[TestPage](    [a] [int] NULL,    [b] [nvarchar](3900) NULL) ON [PRIMARY]

指令碼很簡單，一張表，兩列，一列int類型，一列nvarchar(3900)，一行資料的儲存空間為：3900*2(nvarchar(3900))位元組+4(int)+96位元組(頁頭)+36位元組(行位移）=7932位元組,我們知道一個資料頁儲存的資訊為8K=8192位元組，包括其它消耗所以該表一行資料如果填充完，一行資料將近乎佔據一個資料頁。

我們來添加三行資料，然後查看頁資訊：

--插入三條資料insert [TestPage]values(1,REPLICATE(‘A‘,3900))insert [TestPage]values(2,REPLICATE(‘B‘,3900))insert [TestPage]values(3,REPLICATE(‘C‘,3900))go--查看頁資訊dbcc  traceon(3604)--查看庫中頁集合dbcc extentinfo(CnblogsTestDB,[TestPage])

可以看到，該表中現在有三個資料頁，我們來看看資料頁應該也是近乎沾滿的

 顯示了，通過掃描表資訊，共含有3個資料頁，每個資料頁中的資料量儲存佔比到了96.55%，也就是說基本上是填充滿了。

 當然，我們還可以通過DBCC PAGE命令，來查看每個頁中的具體內容，我們簡單的看一個頁面編號為90的資料頁：

通過上面的命令可以看到，該資料頁中儲存的為表中的第一行的資料，並且在資料庫隱藏檔中是以十六進位方式編碼儲存。

當然，如果感覺此方式不直觀，可以利用一個小工具進行資料頁的查看，這裡我推薦使用Internal Views(此工具在樺仔的博文中有詳細介紹)，可更直觀的展示資料存放區頁資訊：

這裡我們可以點擊我上面上面查看的第一行的資料內容頁進行查看

 

經過上面的分析步驟，其實我的目的是想重現在SQL Server啟動過程中，或者線上上的資料庫經常遇到的經典錯誤824錯誤

上述過程是原理篇，因為我們必須知道資料存放區的底層原理，才能理解好這個錯誤的原因，以及找到正確的處理方法。

 

下一步，我們來重現這個錯誤的原因，我們知道在我建立的測試表中含有兩個欄位：a和b，並且a為int類型、b為nvarchar類型

然後我們介紹了底層的儲存機制，我現在將第一列a欄位的整形資料內容儲存改成字串類型，依次來損壞掉該資料頁內容

我先將服務停掉，然後用檔案編輯工具，修改此資料頁內容，該資料頁內容為十六進位內容，當然在我搞壞這部分資料頁之前我先做一個完整備份

 

然後修改該資料頁資訊，這裡我使用UltraEdit文本編輯工具，開啟檔案，找到該資料頁內容

我們將上面的來源資料更該一下，來把這個資料頁損壞掉

 我們儲存，然後重新啟動該資料庫看看

 這就是我們平常比較常見的824錯誤的過程，而此過程有可能是磁碟壞道造成，或者誤修改檔案等諸多原因，但是此問題還是比較常見的

當然，這種資料頁面的損壞可能造成的影響不是庫層級的，也就說不會造成資料庫不能訪問，其它表是能正常訪問的，但是只是在操作此損壞的資料頁的時候才會報錯，但有時候這幾個資料頁的損壞對業務產生的影響有可能就是致命的，所以我們要解決掉。

鄭重提示：上面過程也可以正確的更改資料頁中的資料，但是如果沒有確切的把握，基本上能把資料庫搞癱瘓掉，我是為了重現問題才修改底層中繼資料，所以在自己的生產庫中千萬不要亂搞！

在資料庫啟動的過程中，會發生一致性校正，所以該錯誤應該會記錄到Error的錯誤記錄檔檔案中，我們來看：

windows平台下的錯誤記錄檔：

當然，在啟動的過程中該問題有可能發生很多，比如磁碟壞道等原因，一系列的資料頁可能就沒法訪問了。所以SQL Server會將這些損壞的頁面記錄到msdb系統庫中，這我們在這個庫中尋找到損壞的頁面集合：

至此，我們已經重現了經典的824錯誤，那我們該如何解決此問題呢？ 

解決方案：

a、如果此問題出現的頁面為資料承載頁，也就說該頁儲存的為內容資料或者為叢集索引的葉子節點資料，並且存在鏡像，版本在SQL Server2005以上，那麼這個錯誤基本可以忽略，SQL Server能夠自動幫你修複此錯誤。

b、如果此問題出現在沒有鏡像的環境中，那就要區分是損壞頁面是否為叢集索引葉子節點資料，如果是，那就簡單了，直接重建索引就好了，如果不是，那此種方案還是不能解決，判斷方法如下：

利用DBCC PAGE命令查看當前資料頁內容，根據ObjectId跟蹤該頁位於哪個對象上，Metdata:IndexID的值判斷是否為索引樹中的節點值，如果大於0則表示為索引值，此時，重建該索引既可以。比如：

我們根據該頁的ObjectID，從資料庫中尋找該頁所屬對象。

 c、如果上述方案都不能滿足，那隻有採取此種方案，我們可以利用Database Backup進行還原，當然為了最大限度的避免資料庫離線，我們最好採取資料頁還原的方式，此種方式最為簡單，還原速度也最快，能夠最大限度的縮短資料庫離線時間，並且保證資料完整性。

這裡提示下：在SQL Server2012版本一下，SSMS不提供映像化資料頁還原方式，在SQL Sever以後的版本中，有映像化介面操作。

所以，我們只能通過如下指令碼進行還原：

RESTORE DATABASE CnblogsTestDBPAGE=‘1:90‘FROM DISK = N‘F:\SQLTest\CnlogsTestDB.bak‘WITH NORECOVERY

當然有交易記錄、更新備份的，需要依次恢複這過程的所有的備份，不要忘記備份尾部日誌。

但是此方法也有局限性：

如果損壞的資料頁為

  1、分配頁：GAM、SGAM和PFS頁

  2、所有資料檔案的啟動頁

如果發生損壞的是以上兩種，則無法通過該備份恢複頁方式進行恢複。如果這種情況下，建議考慮找合適的時間段進行全庫的恢複操作。（推薦）

d、上述情況是在存在有備份的情況下，如果沒有Database Backup，那我們只能選擇最後的一招了，那就是DBCC CHECKDB命令，同樣和上面一樣，此種方式可能會造成資料丟失，所以不建議採用，如果能容忍資料丟失，採用的過程參照文中的上半部分。（不推薦）

 

至此，我們已經完成了一個SQL Server啟動過程或者平常最經常遇到的一個經典錯誤824錯誤，我們來總結下：

824錯誤原因：大部分是由於磁碟儲存導致的資料頁損壞，導致的SQL Server在讀取的時候發生了錯誤。

導致錯誤情境：磁碟壞道、突然斷電等情況下經常會出現此錯誤。

 

----------------------------------------------------------霸氣的分割線-----------------------------------------------------------------------

和824錯誤相關的還有一種是823錯誤，我們來介紹下該錯誤資訊
由於情境所限，我就不重現該錯誤了，在這裡我詳細的介紹下這兩種錯誤的原因和原理，就可以了，如果遇到了，解決的方式基本都是一致的，可參照上面的824錯誤解決方案。

SQL Server在每次寫入頁面的時候，會根據頁面裡的資料算出一個校正值，一同儲存到頁面中去。當下次讀取頁面的時候，再根據這次讀到的頁面資料，算出一個新的校正值。如果寫入和讀出的資料一模一樣，那麼兩個校正值就是相等的。如果兩個校正值不相等，就意味著上次SQL Server寫入的資料和這次讀取出來的一定不同，現在讀取出來的資料就有問題了。

823錯誤就代表著SQL Server在向作業系統申請某個頁面讀寫的時候遇到了Windows讀取或寫入請求失敗。所以該問題的原因大部分是源自於作業系統層面，更確切的說是物理檔案損壞而導致此錯誤，比如裝置驅動程式導致等。

824錯誤則是在讀取資料頁面時候，探索資料頁面有問題，比如讀取出來的校正值不對等。 

當上面描述的823和824錯誤出現大面積的時候，或者直接部分資料檔案完全壞掉的情況下，在SQL Server啟動過程中就會出現資料庫SUSPECT“質疑”狀態。

經過我的多次資料頁的破壞和摧殘，我已經順利的將我們的這個測試庫給搞成了質疑狀態，我們來看SUSPECT（質疑）的狀態庫：

這裡我直接DBCC CHECKDB命令嘗試著恢複下看看

 

所以到此，我們要做的就是避免上述錯誤的發生。如果在生產庫中發生了我上面的情況，然後沒有Database Backup，那麼剩下來你要做的事情：我估計就是準備簡曆了..... 

結語

本篇文章到此結束了......文章主要還是分析SQL Server啟動過程中，載入使用者資料庫的時候，所遇到的一系列問題，文中部分內容需要有一定資料庫基礎知識才能讀懂，篇幅有限，我們沒有做深入的講解分析，比如上面的幾個重要的命令DBCC PAGE....DBCC CHECKDB..等等，隨便一個都能寫出一系列的內容，我們側重的還是問題的解決，和問題原因分析，後續文章中會介紹這一系列的命令作用，以及正確的提示。

....此篇耗時四天完成....文中部分資料庫錯誤都是我耗費精力一步一步調整出來，目的是真實的展現錯誤明細，其實問題解決容易，問題重現的過程複雜。

如果經常使用SQL Server，其實這些問題都是我們會經常遇到的，所以我們要記住相應的解決方案，做的有備無患！

當然個人能力有限，部分不當之處，還望指出不靈賜教。

文章最後給出本篇的關聯篇：

你所不知道的SQL Server資料庫啟動過程，以及啟動不起來的各種問題的分析及解決技巧

 

如果您看了本篇部落格,覺得對您有所收穫，請不要吝嗇您的“推薦”。  

 

你所不知道的SQL Server資料庫啟動過程（使用者資料庫載入過程的疑難雜症）