关闭sleeping连接进程在Sql Server2000数据库存储过程中
不知各位有没遇到过这样的问题，比如在java中，你每次都关闭了数据库连接如：conn.close.但你查看数据库进程中仍有好多sleeping，随着访问次数的增加这种连接会越来越多，最终可能会导至你的数据库拒绝连接。解决方法就是结束提这些连接，下面的存储过程就是实现这个功能。当然，你完全可以将其写进一个定时调度里同。
--**************************************
--
-- Name: Deleting Sleeping Processes In
-- SQL SERVER 2000
-- Description:Sometimes We Develop Appl
-- ications Where we Create a lot of DB Ins
-- tances . Those Instrances remain sleepin
-- g on The Server DB and usually makes Hin
-- drances in Using Connection Pooling Effi
-- ciently .
-- By: Kamal Kant Singh
--
-- Inputs:no Input Parameters . This is
-- A Stored Procedure which can be put in a
-- job to run it Again & again
--
-- Returns:Nothing
--
-- Side Effects:No Side Effects .
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=1109&lngWId=5--for details.--**************************************
--
CREATE PROCEDURE KillMyJobs
-- Author :---Kamal Kant Singh / Amarjee
-- t Singh
-- +91-9810481159 / 9910127650
AS
BEGIN
--Tempopary Table for Holding All Values
-- From SP_WHO
CREATE TABLE #Temp(spID int,ecid int,status varchar(50),loginname varchar(100),hostname varchar(100),blk int,dbname varchar(100),cmd varchar(100))
INSERT INTO #TEMP EXEC sp_who
DECLARE @ID varchar(100)
DECLARE @STR varchar(1000)
SET @ID = ''
SET @STR = ''
-- Createting a Cursor For Holding Only
-- JobID whose STATUS = 'sleeping' AND dbNA
-- ME = 'LODHI'
DECLARE TEMPCUR CURSOR FOR
SELECT spId FROM #Temp WHERE STATUS = 'sleeping' AND dbNAME = 'LODHI'
OPEN TEMPCUR
FETCH NEXT FROM TEMPCUR INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ID
--PRINT 'kamal'
SET @STR = 'KILL ' + @ID
--PRINT @STR
EXEC(@STR) -- Killing The Sleeping Jobs One BY One In The Lodhi DB
FETCH NEXT FROM TEMPCUR INTO @ID
END
CLOSE TEMPCUR
DEALLOCATE TEMPCUR -- Closing & Deallocating The CURSOR After Doing our WORK Successfully
DROP TABLE #TEMP -- Dropping The TEMPORARY Table Created
END
不知各位有没遇到过这样的问题，比如在java中，你每次都关闭了数据库连接如：conn.close.但你查看数据库进程中仍有好多sleeping，随着访问次数的增加这种连接会越来越多，最终可能会导至你的数据库拒绝连接。解决方法就是结束提这些连接，下面的存储过程就是实现这个功能。当然，你完全可以将其写进一个定时调度里同。
--**************************************
--
-- Name: Deleting Sleeping Processes In
-- SQL SERVER 2000
-- Description:Sometimes We Develop Appl
-- ications Where we Create a lot of DB Ins
-- tances . Those Instrances remain sleepin
-- g on The Server DB and usually makes Hin
-- drances in Using Connection Pooling Effi
-- ciently .
-- By: Kamal Kant Singh
--
-- Inputs:no Input Parameters . This is
-- A Stored Procedure which can be put in a
-- job to run it Again & again
--
-- Returns:Nothing
--
-- Side Effects:No Side Effects .
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=1109&lngWId=5--for details.--**************************************
--
CREATE PROCEDURE KillMyJobs
-- Author :---Kamal Kant Singh / Amarjee
-- t Singh
-- +91-9810481159 / 9910127650
AS
BEGIN
--Tempopary Table for Holding All Values
-- From SP_WHO
CREATE TABLE #Temp(spID int,ecid int,status varchar(50),loginname varchar(100),hostname varchar(100),blk int,dbname varchar(100),cmd varchar(100))
INSERT INTO #TEMP EXEC sp_who
DECLARE @ID varchar(100)
DECLARE @STR varchar(1000)
SET @ID = ''
SET @STR = ''
-- Createting a Cursor For Holding Only
-- JobID whose STATUS = 'sleeping' AND dbNA
-- ME = 'LODHI'
DECLARE TEMPCUR CURSOR FOR
SELECT spId FROM #Temp WHERE STATUS = 'sleeping' AND dbNAME = 'LODHI'
OPEN TEMPCUR
FETCH NEXT FROM TEMPCUR INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ID
--PRINT 'kamal'
SET @STR = 'KILL ' + @ID
--PRINT @STR
EXEC(@STR) -- Killing The Sleeping Jobs One BY One In The Lodhi DB
FETCH NEXT FROM TEMPCUR INTO @ID
END
CLOSE TEMPCUR
DEALLOCATE TEMPCUR -- Closing & Deallocating The CURSOR After Doing our WORK Successfully
DROP TABLE #TEMP -- Dropping The TEMPORARY Table Created
END
