災難救援演練 - 如何修復一個 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

KILL (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms173730.aspx
DBCC INPUTBUFFER (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms187730.aspx
sys.dm_exec_sessions (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176013.aspx

關鍵字：SQL Server、Kill Session、DBCC CHECKDB、rollback_immediate