Solved

sql2000 having so many process with sleeping status

Posted on 2007-03-24
3
992 Views
Last Modified: 2012-05-05
Hi,
Currently sql process showing 1500+ process which most of them are with "sleeping" status and only 20 processes are running, I would like to know how could I kill all the "sleeping" status without restarting the mssqlserver services  and I dont think kill the spid one by one is the greate idea, anyone could help me here??
0
Comment
Question by:motioneye
3 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18785617
The quickest way to kill them all would be to terminate the application that has them open. It sounds as if whatever code is connecting to the database is not explicitly disposing of the database connections.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18785633
0
 

Author Comment

by:motioneye
ID: 18785731
Hi,
I install the sp and run it in test server which only having 25 processes with 10 process with sleeping status and need to be kill but it takes really really long times and it end up with I stop the query, do u know why???

CREATE PROCEDURE dbo.KillAllProcesses @dbName varchar(50)


AS

BEGIN

SET NOCOUNT ON

DECLARE @spid INT,

@cnt INT,

@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

WHILE @spid IS NOT NULL

BEGIN

SET @sql = 'KILL ' + RTRIM(@spid)

EXEC ( @sql )

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

END

END

GO
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP installation issues 11 59
SQL Syntax 5 34
sql server insert 12 30
[SQL server / powershell] bulk delete table from CSV 8 29
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question