x

idle users connected to the server in sql server 2005 snt edition

hi team, i have executed the below query select * from sys.dm_exec_sessions where dateadd(hour, 2, last_request_end_time) < getdate() order by last_request_end_time desc and found that more than 300 users are connected to the server,who are purely idle,.my question it that what is the saffest way to delete them, i am thinking to use this command alter database DbaWorks set online with no_wait is there any other best to to kill idle users
more ▼

asked Sep 27, 2010 at 06:38 AM in Default

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
I wouldn't necessarily remove the connections. It might just be connection pooling waiting for the next round to come through. As long those connections aren't holding locks and there are only a few of them (300 isn't much), I wouldn't sweat it necessarily. If they're holding locks or the number escelates by quite a bit, then you have cause for serious concern.
more ▼

answered Sep 27, 2010 at 07:02 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Same advice - I would only kill connections that had been idle for a lengthy period of time. The length of time depends on the database and application - thus one should understand those things.

Be careful before trying to kill any connections. It is well worth it to gain an understanding of the app and the database before killing these connections.
more ▼

answered Sep 27, 2010 at 12:03 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

(comments are locked)
10|1200 characters needed characters left

SQL Server thoughtfully provides a [KILL][1] command in T-SQL...

However, you might want to make sure that they really are up to nothing. I used to only kill connections that had been idle for 24 hours...

[1]: http://msdn.microsoft.com/en-us/library/ms173730(v=SQL.90).aspx
more ▼

answered Sep 27, 2010 at 06:43 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x715
x84
x51

asked: Sep 27, 2010 at 06:38 AM

Seen: 1424 times

Last Updated: Sep 27, 2010 at 06:38 AM