[SQL server] 解決將資料庫 Offline 時出現的錯誤

有時進行維護時, 總會須要將系統offline. 但有時會出現錯誤指TABLE 被Lock. 這是因為執行ALTER TABLE 時, 會見到有process 仍在執行.若要檢查是什麼process, 可以執行以下SQL server 指令.

EXEC sp_who2;

之後再檢查欄DBName, 找出相對應的SPID, 之後再執行KILL 指令.

kill <<SPID>>;

通常有關的SPID 都不只一個, 若要批量形式 KILL 的話, 可以利用SQL script 如下

declare @DBName varchar(60);
declare @killCommand varchar(60);
declare @id int;

set @DBName='<<TargetDataBase>>';

declare processIdCursor cursor for
select P.spid
FROM master..sysprocesses P,
master..sysdatabases D
WHERE
D.dbid = P.dbid
and d.NAME=@DBName
;
open processIdCursor
FETCH NEXT FROM processIdCursor INTO @id 
WHILE @@FETCH_STATUS = 0
begin
	set @killCommand = 'KILL ' + convert(varchar(8) ,@id)+';';
	print @killCommand;
	exec(@killCommand);
	FETCH NEXT FROM processIdCursor INTO @id 
END   
CLOSE processIdCursor   
DEALLOCATE processIdCursor

 

About C.H. Ling 262 Articles
a .net / Java developer from Hong Kong and currently located in United Kingdom. Thanks for Google because it solve many technical problems so I build this blog as return. Besides coding and trying advance technology, hiking and traveling is other favorite to me, so I will write down something what I see and what I feel during it. Happy reading!!!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.