SSMS報1222錯誤

標籤：故障排除

1. 問題

前幾天在其中一個QQ群裡，群友在SSMS嘗試開啟表或預存程序對象列表瀏覽時，遇一個1222的錯誤，根本無法瀏覽系統對象。

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M01/7E/76/wKioL1cBR6egDZ1bAAC_sKAv7Ug475.jpg" title="01.jpg" alt="wKioL1cBR6egDZ1bAAC_sKAv7Ug475.jpg" />

Figure-1: SSMS報1222錯

乍一看，我首先反應是可能是串連遠端SQL Server；如果本地的也出現這種錯誤，估計是系統對象被鎖。後來經過測試，果然把這個錯誤重視出來。

2. 重現與分析問題

建立一個預存程序，預存程序裡面使用了明確交易，但不提交事務，建立時沒有報錯。但執行時會報錯。如果這裡這個預存程序被刪除了，就會出現這個報錯。

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M02/7E/7A/wKiom1cBSK-hn2u-AAF2r3ol-IY680.jpg" title="02.jpg" alt="wKiom1cBSK-hn2u-AAF2r3ol-IY680.jpg" />

Figure-2: 建立測試的預存程序

執行預存程序，報了一個錯誤。提示BEGIN和COMMIT數目不配置。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/7E/76/wKioL1cBSZzS4wtJAAGoEmGN88g120.jpg" title="03.jpg" alt="wKioL1cBSZzS4wtJAAGoEmGN88g120.jpg" />

Figure-3: 執行儲存

最後刪除預存程序，沒有報錯。

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M01/7E/7A/wKiom1cBS6Lw6edMAAD8saPST98930.jpg" title="04.jpg" alt="wKiom1cBS6Lw6edMAAD8saPST98930.jpg" />

Figure-4: 刪除預存程序

這時，重新整理下SSMS的Object Exploer指定的資料庫，然後去開啟表或預存程序的列表，就會報1222錯誤。查詢相關的鎖，發現測試的預存程序使用的表被和相關頁面被鎖。

USE AdventureWorks2008R2GOSELECT  lo.request_session_id AS [session_id],DB_NAME(lo.resource_database_id) AS [database_name],lo.resource_type,lo.resource_subtype AS [subtype],lo.resource_description AS [description],lo.request_mode as [mode],lo.request_owner_type as [owner_type],lo.request_status as [status],CASE WHEN lo.resource_type = ‘OBJECT‘ THEN OBJECT_NAME(lo.resource_associated_entity_id)   WHEN lo.resource_associated_entity_id IS NULL OR lo.resource_associated_entity_id = 0 THEN NULL   ELSE OBJECT_NAME(p.[object_id])  END AS associated_entity,wt.blocking_session_id,wt.resource_descriptionFROM sys.dm_tran_locks AS loLEFT JOIN sys.partitions AS pON lo.resource_associated_entity_id = p.partition_idLEFT JOIN sys.dm_os_waiting_tasks AS wtON lo.lock_owner_address = wt.resource_addressWHERE lo.request_session_id > 50AND lo.resource_database_id = DB_ID()AND lo.request_session_id <> @@SPIDORDER BY session_id ASC,resource_type ASC;GO

Code-1: 查詢鎖

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/7E/7A/wKiom1cBT3LxwQZUAAMK3-cQImg945.jpg" title="05.jpg" alt="wKiom1cBT3LxwQZUAAMK3-cQImg945.jpg" />

Figure-5: 查詢結果

3. 解決與結論

找到執行預存程序的session id，然後Kill掉就行了。所以，使用明確交易時，一定要小心事務要提交或復原，尤其是使用了多個BEGIN TRAN時。問題是解決了，還是忍不住上網再找下有沒有人遇到過類型的問題。果然，人家很久之前就討論過了。請參考：

http://www.sqlservercentral.com/Forums/Topic310216-5-1.aspx

https://support.microsoft.com/en-us/kb/308518

本文出自 “FishParadise” 部落格，請務必保留此出處http://fishparadise.blog.51cto.com/11284420/1760036

SSMS報1222錯誤