2012年1月18日 星期三

災難救援演練 - 如何修復一個 Kill Session 後,發生問題的資料庫

        最近遇到大家常遇到的一個問題,就是當資料表在進行大量的更新 (Update) 或刪除 (Delete)時,由於執行過久,所以強制透過 Kill 的語法進行刪除,但是有時候由於語法可能只執行到一半,所以當被強制的 Kill 時,SQL Server就會很聰明的自動進行 Rollback 的動作,但是有時候就會非常的久,大部份的使用者如果耐不住性子時,就會重啟系統並強制關閉,這樣的流程其實非常的危險,所以我在正確的方法與流程整理如下,以免大家因為操作不慎造成資料的損失。

問題重現:

1、先將範例的資料庫 [AdventureWorks2008R2] 裝好,裝法與設定方式請參考我之前的文章 (安裝SQL Server 中的範例資料庫)。

2、由於為了快速將問題重現,所以我特別將表格 [SalesOrderDetail] 的資料重覆填入到另一個新的資料表中,讓這個資料表的筆數達到約1千二百萬筆左右。

3、執行更新語法進行資料的變更。
UPDATE [AdventureWorks2008R2].[Sales].[SalesOrderDetail2]
set UnitPrice = UnitPrice + 0.1
where UnitPrice < 100

4、由於執行約需要3分鐘左右,所以我們先透過下列的 DMV 語法查出目前所有執行的 Session。
select * from sys.dm_exec_sessions
where session_id > 50;


PS:因為 session_id 小於50都是屬於SQL Server的內部執行,所以可以略過。

5、然後找出目前的 session_id 然後再透過下列的指令查出此 session 的內容。
dbcc inputbuffer(59);


6、確定是我們正在執行更新語法的執行緒後,我們模擬使用者的情況,因為等不及了,所以透過 Kill 的指令將 session 強制砍掉。
kill 59


7、執行完成後,你會發現系統正在進行 Rollback,此時我們馬上進行關機,因為要模擬使用者等不及的狀態。

8、此時你就會發現資料庫會進入到 復原中 (In Recovery) 的狀態中。



經過上述的還原步驟,主要是模擬使用者的情況,接下來我們要介紹遇到此狀況時,該如何進行還原。

正確修復步驟:

1、當你執行完 Kill 的指令後,請先透過下列的指令切換到單人模式,加快還原的速度,如果執行後30分鐘內仍未完成,再請進行下一個步驟。
--切換成單人模式
alter database <database_name> set single_user with rollback_immediate
go
--切換回多人模式
alter database <database_name> set multi_user
go

2、重新啟動SQL Server Service。

3、如果重啟後,資料表仍然無法存取時,再請進行下列的步驟。
  • 停止 SQL Server Service
  • 將資料庫的mdf與ldf檔,搬移到暫時目錄,例如C:\Temp
  • 然後啟動SQL Server服務
  • 使用SQL Server Management Studio將此資料庫刪除
  • 使用您的備份檔還原資料庫

4、完成上述的動作後,建議再進行下列的指令進行資料庫完整性的檢查。

  •   開啟 SQL Server Management Studio
  •   輸入DBCC CHECKDB(‘database name’);

基本上將 SQL Server的服務重啟後,問題就會解決,但建議最好執行 DBCC CHECKDB的指令檢查資料庫的完整性,藉以確保資料庫日後的運行。


參考連結:
DBCC CHECKDB (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms176064.aspx
ALTER DATABASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174269.aspx
關鍵字:SQL ServerKill SessionDBCC CHECKDBrollback_immediate

沒有留言:

張貼留言