Closing sleeping connection process in SQL Server2000 Database storage process)

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.