October 14, 2009 at 1:11 am
Hi
i have a problem with too many sleeping users with conncections in the Sqlserver2005. i want to kill all users who are sleeping and idle time is greater than two hour. individullay kill spid statement can not work for too many users. i googled i got the scripts, but i belongs to particular database or login. i want to kill all users in all databases who are sleeping and idle time greater than two hours.
can any one guide me on this.
Thanks
Rock..
October 14, 2009 at 4:25 am
I think you should look at sleeping processes, not users.
I wonder why you think sleeping processes are a problem? How do you measure that? And how many processes are we talking about? Also realise, that processID's =<50 are SQL Server processes, better don't touch those.
You could use some t-sql like this(found it on the internet):
DECLARE @v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status='sleeping'
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@@spid
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'
EXEC('KILL '+@v_spid)
FETCH NEXT FROM c_Users INTO @v_spid
END
CLOSE c_Users
DEALLOCATE c_Users
Now, I would not want to use a cursor, but I can't be bothered now to re-write this.
HtH
Greetz,
Hans Brouwer
October 16, 2009 at 1:32 am
Actually, i want to close the connections who are connected for long hours and their status is sleeping. because of too many connections in the server.
can you guide me
Thanks
Rock..
October 16, 2009 at 1:36 am
rockingadmin (10/16/2009)
Actually, i want to close the connections who are connected for long hours and their status is sleeping. because of too many connections in the server.can you guide me
Thanks
Rock..
I am curious to what you consider too many connections to the server
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 2:09 am
some of the developers working in .net application and specified the min and max connections limit. the problem is they didn't close the connections properly. when the connections reached to maximum. they are not able to connect to the server. for that i need to close the connections who are sleeping and idle time greater than 2 or 3hrs. I know you will ask this question,why you didn't ask them to close the connections properly?
I can say, But i want to check from my end also.
Hope you understand
Thanks
Rock..
October 16, 2009 at 2:25 pm
I know you will ask this question,why you didn't ask them to close the connections properly?
Give us their email id's we will order them to close it.....LOL
MJ
October 16, 2009 at 2:34 pm
I have to admit, reading the phrase, "I want to kill all users who are sleeping" brings to mind something significantly different to my sleep-deprived brain than the actually intended meaning.
Personally, I generally wouldn't worry about sleeping connections. They really don't matter much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply