求購SQL語句(100分)

因事務處理過多,用戶也不少,導致SQL Server數據庫出現死鎖,想通過sql語句在查詢分析器中結掉死鎖的進程!

分布式事务处理.
再不就用oracle了... 一般的访问量sqlserver是没有问题的.

我們的用戶較多,約400個

400个不算多吧..

CREATE PROCEDURE spKillProcess @dbname char(256)
AS
declare @killid int

declare tmpkilldb cursor for
select distinct p.spid FROM master..sysprocesses p ,master..sysdatabases d (nolock),master..syslocks l
where p.dbid= d.dbid and d.name=@dbname and p.spid = l.spid and (p.spid in (select distinct blocked from master..sysprocesses where blocked>0))

open tmpkilldb

fetch tmpkilldb into @killid

while @@fetch_status >= 0
begin
declare @KillString char(256)
select @KillString ='Kill ' + Convert(char(4),@killid )
exec(@KillString)
print @KillString

fetch tmpkilldb into @killid
end

deallocate tmpkilldb
GO

to:zhlu
多謝,正在測試中(很不巧,現在數據庫還未出現鎖定),如果正確,立即放分,謝謝!

to:zhlu
剛剛測試到你sql語句,但顯示如下錯誤:asp_killprocess parameter '@dbname' not found
明明給參數賦了值,為何如此呢?

我的調用方法:
1>將你的存貯過程存放在服務器master數據庫中
2> with asp_killprocess do //asp_killprocess是Tadostoreproc
begin
close;
asp_killprocess.ProcedureName:='spKillProcess;1';
parameters.ParamByName('@dbname').Value:=trim(tsg_detail.Cells[5,tsg_detail.Row]);
try
execproc;
except
application.MessageBox('Kill process error','Server Monitor');
end;
end;

to:zhlu
通過語句啟發,已經解決了問題,非常感謝,現在放分了!