Closing sleeping connection process during SQL Server2000 Database storage process
I don't know whether you have encountered such a problem. For example, in Java, you close the database connection every time, such as Conn. close. however, you still have a lot of sleeping operations in the database process. As the number of accesses increases, these connections will increase and may eventually lead to your database to reject the connection. The solution is to end these connections. The following Stored Procedure implements this function. Of course, you can write it into a scheduled scheduling.
--**************************************
--
-- Name: deleting sleeping processes in
-- SQL Server 2000
-- Description: Sometimes we develop appl
-- Ications where we create a lot of DB INS
-- Tances. Those restart ances 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
-- 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
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