SQL 問答集
分散式的交易、 效能計數器與 SQL 備份
Paul S。 Randal
問題： 我們使用很多的分散式交易，而且我們現在調查來提供高可用性，其中一個我們關鍵資料庫鏡像的資料庫。 在我們的測試，我們發現的分散式的交易有時失敗之後我們試著容錯移轉到鏡像資料庫。 您可以解釋什麼進行？
答案是： 這是實際上記載的限制的使用分散式的交易。 限制存在時使用資料庫鏡像或記錄傳送 — 基本上 Windows 伺服器名稱不同的執行容錯移轉後任何技術。
使用 [Microsoft 分散式交易協調器 (MSDTC) 的交易時本機交易協調器，有一個識別執行所在伺服器的資源識別碼。 鏡像容錯移轉時主體資料庫變成裝載於不同的伺服器 (鏡像的夥伴)，而因此的交易協調器資源識別碼是不同。
如果分散式的交易為作用中上鏡像, 的交易協調器交易夥伴嘗試確定交易的狀態並 can’t 因為它有錯誤的資源識別碼; MSDTC doesn’t 認為它是它最初 wasn’t 參與分散式交易。 分散式的交易必須結束在這種情況下 — 您看到的行為。
具有跨資料庫交易 (簡單交易牽涉到多個資料庫中更新)，就會發生類似的問題。 如果其中一個相關資料庫鏡像處理，且其中一個 isn’t ’s 可能跨資料庫交易認可這兩個資料庫中。 如果 (當不同步主體和鏡像，並允許資料遺失一個手動容錯移轉執行)，就會發生強制鏡像容錯移轉，鏡像的資料庫中認可的交易可能會遺失 — 破壞跨資料庫交易的完整性。
如果鏡像的資料庫未同步 (請參閱 的 六月 2009 年欄 位置我解釋這更)，因此已認可的跨資料庫交易記錄檔資料錄尚未尚未傳送到鏡像，就會發生這個問題。 在強制容錯移轉之後交易不存在於新主體資料庫也因此跨資料庫交易的完整性中斷。
問題： 最近我已監視一些效能計數器，以找出我們的資料庫儲存區發生問題。 同時執行這項操作我注意到很奇怪的東西：雖然已在資料庫中進行的執行任何動作，時發生仍然發生資料庫檔案的寫入活動。 這剛好為資料和記錄檔。 我甚至做確定有個沒有連線到 SQL Server，但它仍然繼續。 如何有 I/O 活動時很沒有連接嗎?
答案是： SQL Server 有一些需要執行的房子保存作業 ； 這些稱為背景工作。 一或多個這些是做的事您系統上，且造成 I/O。 這裡 ’s 快速可能禍首清單：
幽靈車清理： 將刪除作業只記錄標示為已刪除，作為效能最佳化，以防作業取消 ； 它並不實際零出空間。 一旦刪除作業已經認可，東西有實際從資料庫移除已刪除的資料錄。 這是由幽靈車清除工作。 您可以閱讀更多關於該在我的部落格上的 這個張貼 ，其中也說明如何請檢查是否正在執行無反應清除工作。
自動壓縮： 這是您可以開啟自動移除空白空間在資料庫中的工作。 它的運作方式是將頁面從資料檔的結尾移至開始合併在結尾的可用空間，並再截斷檔案。 雖然您可以開啟它的您絕對不應該 — 它造成索引片段的問題 (導致效能不佳)，並使用大量資源。 通常，您將需要自動成長的一個資料庫啟用，太，所以您可以帶入壓縮-成長-壓縮-成長的循環，執行許多沒有取樣的工作。 您可以檢查與此查詢的所有您資料庫的狀態：
SELECT name, is_auto_shrink_on FROM sys.databases;
延期-置放： 這項工作會負責執行卸除或截斷資料表和索引所需的工作 (索引拖放可能是因為索引重建作業 — 建立新的索引，然後舊卸除)。 小型資料表與索引，de-allocation 會立即完成。 較大的資料表與索引，de-allocation 會由背景工作在批次中完成。 這是記憶體的為了確保可以不需要執行時取得所有必要的鎖定。 您可以使用各種延期拖放效能計數器來監視此任務如下所述線上叢書 》 這裡 。
延遲寫入: 這項工作會負責從記憶體中快取 (稱為緩衝集區) 移除舊的網頁。 當伺服器記憶體壓力之下時頁面可能要被移除，即使它們在其上有變更。 在這種情況下變更的頁面必須被寫回磁碟從 Outlook 記憶體移除前。 您可以使用該 「 延遲寫入數 / 秒 」 來監視這項工作，線上叢書 》 所述的效能計數器 這裡 。
這些都可能對資料庫進行變更。 它們都使用交易來讓所做的變更，並且磁碟上每當交易認可，交易所產生的交易記錄檔記錄必須寫到資料庫記錄檔部分。 定期經常資料庫正在對變更，檢查點也必須發生排清出變更的資料檔案頁面到磁碟。 您可以閱讀更多關於這我在二月 2009 年問題的 瞭解記錄和 SQL Server 中的修復 上的 TechNet 雜誌 」 文件中。
如您所見，只是因為有沒有作用中的連線至 SQL Server，一定 doesn’t 表示程序是 quiescent — 可能是忙著處理一或多個背景工作。 如果 I/O 活動持續長在所有資料庫活動完成之後，您也可能要檢查可能正在執行的排程作業。
問題： 我 ’m 非自願 DBA，我被嘗試不同的項目是最快速。 先前 DBA 設定才能備份檔案作業，但我 can’t 想出如何加以還原。 有，查看備份檔案中有任何方法嗎？ 以及如何可以我將它們還原正確？
答案是： 雖然可以將備份附加至相同的檔案，大多數人會將每個備份放在不同的檔案，有意義的名稱 (，通常是日期/時間戳記組合)。 這有助於避免的問題面對，讓它容易執行其他工作：
- 當每個備份在自己的檔案時，複製安全的備份很容易的。 如果所有的備份是在單一檔案的最新的備份複本可以只進行複製整個備份的檔案。
- 刪除舊的備份時，無法可能所有的備份是在單一檔案中。
- 不小心覆寫現有的備份 isn’t 可能如果每個備份有分別命名的檔案。
不幸的是，doesn’t 幫助您的情況 — 已經有其內的多個備份的檔案。 不過，有兩種方法可以還原備份：手動或使用 SQL Server Management Studio (SSMS)。
若要查看備份是在檔案中，您可以使用 SSMS，若要建立新的備份裝置所參考檔案。 一旦建立參考，您可以顯示是什麼 ’s 這個備份裝置中備份的詳細的資料。 或者您可以使用 RESTORE HEADERONLY 命令。 這兩者會檢查備份裝置，並提供一行描述每個備份檔案中的輸出。 SSMS 識別備份類型使用好記的名稱。 使用正確的語法，您需要找出其中一個藉由使用命令 SQL Server 線上叢書 》 的項目中所提供的資訊是什麼類型的每個備份 (請參閱 這裡 SQL Server 2008 版本) 以便您可以使用適當 RESTORE 命令還原備份。
您也必須運作出哪個備份您想要還原。 這是有點棘手，因為您需要從 RESTORE HEADERONLY 輸出資料行名稱不符合您必須使用來還原它的選項。 備份檔中分別編號為 1 正在最舊的 1，且數目可以找到稱為位置的資料行中。 若要還原該備份，您必須使用數字中 WITH FILE = < 號碼 > 部份 RESTORE 命令。 以下是範例：
RESTORE DATABASE test FROM DISK = 'C:\SQLskills\test.bak' WITH FILE = 1, NORECOVERY;RESTORE LOG test FROM DISK = 'C:\SQLskills\test.bak'
WITH FILE = 2, NORECOVERY;
依此類推。 您必須以資料庫備份開始還原順序，然後再還原零個或多個差異式資料庫及/或交易記錄檔備份。 進一步的詳細資料不在本專欄的範圍之內，但您可以閱讀更多關於還原順序和其他您可能需要在十一月 2009 年問題上 修復嚴重損壞使用備份從 我文件中的還原選項。
使用 SSMS，在還原資料庫精靈中指定備份檔案，它會自動顯示在檔案中的所有備份並可讓您選取您想要的。 [圖 1] 顯示其中一個範例。
.jpg)
圖 1 使用 SSMS 還原資料庫精靈，以顯示多個備份中 的檔案。
無論您選擇哪一個選項它 ’s 重要之前要為真正做它，當從嚴重損壞修復練習執行還原到其他位置。 我最喜歡的其中一個原則是 「 您是否沒有備份直到完成成功的還原 」
問題： 我有我需要將每隔幾週後複製至開發環境的非常大型資料庫。 我的問題是應用程式為了更多的資料傳入期待已最近成長資料庫，而且現在它 ’s 太大當還原在開發環境中。 我要如何取得它才能較小，當我還原?
答案是： 這是相當常見的問題，並沒有，不幸的是，好的答案。
一個資料庫備份並不會改變資料庫以任何方式 — 只是讀取資料庫的所有使用的部份，並包含在備份以及交易記錄檔的一些 (在我的原因說明的部落格上看到 這個張貼 及有多少)。 從資料庫備份還原只是建立檔案、 寫出什麼是在備份並接著會在資料庫上執行復原。 基本上，您必須在資料庫中會是什麼時，就可以取得您還原。 有 ’s 沒有壓縮上還原資料庫、 還原、 在 [還原] 上, 更新統計資料或其他人可能會想要執行的作業上的地址索引片段的選項。
如此若要達成什麼您要如何？ 您了三個選項取決於您確切的案例。
第一次，您可以執行壓縮作業生產資料庫以收回空白空間上。 這會使資料庫已還原的複本相同為其中一個，生產和沒有浪費的空間但在潛在高成本。 實際執行資料庫就必須再次，成長而且壓縮作業可能是非常昂貴 (方面 CPU、 I/O 和交易記錄檔)，而且會導致索引分散。 索引片段就必須加以處理花更多的資源。 這不是您想要使用的選項。 (更深入說明使用資料檔的 perils 縮小，請參閱 這個張貼 上我的部落格)。您可以考慮 只 移除結尾的檔案 (DBCC 式 WITH TRUNCATEONLY) 的可用空間，但這可能會不讓您減少在您需要的大小。
第二個，如果您只需要還原一次在開發實際執行資料庫，需要空間不足，無法還原觀看完整大小的資料庫，然後將它收回該空間縮小。 在那之後您需要決定是否要位址由壓縮作業所建立的片段。
如果你們會執行查詢或報告的效能測試，片段可能造成大拖放在這些查詢效能。 如果您不 ’re，您可能不需要完全移除片段。 若要解決片段，您 can’t 重建索引 (使用 ALTER INDEX … 重建] 命令)，需要額外的空間，並會導致再次成長資料庫 — 您需要重新組織索引 (使用 ALTER INDEX … 重組] 指令)。
如果您決定要移除片段，請注意資料庫切換成 SIMPLE 復原模型因此交易記錄檔並不增加從產生的重新組織的所有交易記錄檔資料錄。 如果您將資料庫留在 FULL 復原模式時，記錄檔仍會繼續成長除非製作記錄檔備份 — 您可能會想要避免處理資料庫的一個開發複本中的東西。
最後，如果需要還原生產資料庫多次在開發過程中的您不打算要重複顯示在選項 2 多次步驟。 在這種情況下會依照選項 2 中，然後建立進一步的 shrunk (和也許重組) 資料庫備份的最佳方法。
這個第二個備份可以再用於執行的最小大小的實際執行資料庫的多個還原。
合併彙算，沒有移動而不需要的初始還原 thSQLat 可用空間有很多的可用磁碟空間來開發環境的實際執行資料庫簡單方法。
感謝至 Kimberly L。 Tripp 的 SQLskills.com 提供技術檢閱這個月 ’s 資料行的!
Paul S. Randal 是 SQLskills.com 的 管理導演 Microsoft 地區導演和 SQL Server 中 MVP。他曾在 SQL Server 儲存引擎小組在 Microsoft 從 1999 年至 2007 年。Randal DBCC CHECKDB/修復撰寫的 SQL Server 2005，並在 SQL Server 2008 開發期間已負責核心儲存引擎。他是在嚴重損壞修復、 高可用性和資料庫維護的專家，並定期主持人演說全球。他在 SQLskills.com/blogs/paul 的部落格，您可以找到他上 Twitter 在 Twitter.com/PaulRandal 。