求 数据库阻塞 完整解决方案
近日,我们的MSSQL数据库经常因阻塞而导致用户无法正常访问.急寻   遇到此问题时的完整解决方案.谢谢,送上我所有的分.

------解决方案--------------------
--在查询分析器中执行：sp_wholock

--转载：


CREATE PROCEDURE dbo.sp_wholock
AS

--create procedure sp_WhoLock with ENCRYPTION as
-------------------------
-- 一、根据sp_who改编产生SPID对应的用户#who表
-------------------------

if (object_id( 'tmp_dbuser ') is not null)
drop table tmp_dbuser

if (object_id( 'tempdb..#tb1_sysprocesses ') is not null)
drop table #tb1_sysprocesses

declare @loginame sysname
set @loginame= NULL
set nocount on

declare @retcode int

declare @sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare @charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)

select @retcode = 0 -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select @spidlow = 0 ,@spidhigh = 32767

IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end

IF (lower(@loginame) IN ( 'active ')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end

IF (patindex ( '%[^0-9]% ' , isnull(@loginame, 'z ')) = 0) --Is a number.
begin
select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN

LABEL_17PARM1EDITED:

-------------------- Capture consistent sysprocesses. -------------------

SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame)) as loginname
,spid as 'spid_sort '
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char '
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)

--------Screen out any rows?

IF (@loginame IN ( 'active '))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping ' and
upper(cmd) IN (
'AWAITING COMMAND '
, 'MIRROR HANDLER '
, 'LAZY WRITER '
, 'CHECKPOINT SLEEP '
, 'RA MANAGER '