--当很多连接到你的数据库时,报这种错误“已超过了锁请求超时时段”
--直接把下在面的存储过程 放到你的Master数据库执行一下就可以了
USE DBName
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
create PROC [dbo].[Sp_KillAllProcessInDB] @DbName VARCHAR(100)
AS
IF DB_ID(@DbName) = NULL
BEGIN
PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
DECLARE @killspId VARCHAR(30)
DECLARE TmpCursor CURSOR
FOR
SELECT 'Kill ' + CONVERT(VARCHAR, spid) AS spId
FROM master..SysProcesses
WHERE DB_NAME(dbid) = @DbName
AND spid <> @@spid --终止不是当前进程的进程(不等于当前进程)
AND dbid <> 0 --数据库ID不是0
OPEN TmpCursor
FETCH NEXT FROM TmpCursor
INTO @killspId --select into @killspId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@killspId)
FETCH NEXT FROM TmpCursor
INTO @killspId
END
CLOSE TmpCursor
DEALLOCATE TmpCursor
END
--exec Sp_KillAllProcessInDB 'DBName'
--查询死锁
select * from master..SysProcesses where db_Name(dbID) = 'DBName'
and spId <> @@SpId
and dbID <> 0
and blocked >0;
