Keep Alive Setting?

Hi,

In SQL Server 2000, is that any option to set keep session/connection alive for at least 2 hours (without any activities from/to the server)? Or the setting on Windows server?

I have tried to search on the forum for this similar topic, but it keeps give me a timed out error.

Thanks in advanced.

Regards,
Andy

Hi Satya, sorry i don't get your meaning. can you be more specific?
I have the problem with the user that usually leave the application open to do some are manual task, and then after they back to the application (around 15 minutes - 2 hours later), the application has been terminated from server.

Regards,
Andy

Satya, Actually it happened in almost all client. So how do i troubleshoot this? What setting that i should modify in SQL Server and Windows Server ? I try to avoid the setting on client, since it will consume a lot of job.

Regards,
Andy

It is a in-house developed application.
In my application, there is a global connection (suppose to always connected to the server), and one or two more connections on entry form/screen.
For global connection, will only close whenever user quit from application
For each entry form/screen, connection(s) will close whenever user quit from entry form/screen.

There is no other terminate connection except those stated above, so i suspect of keep alive setting or something else.

What is the language used for developing the application?
There is no setting in sql server and it is in the frond end application check with application developers...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Mohammed, Our front end application was built using Ms. Visual Foxpro and use ODBC connection to SQL Server.

Satya, sometimes terminated connection will still in the list of active connections (SP_WHO2), eventhough the connection is already terminated. Based on the experience of some users, they will get the connection terminated whenever they let application open without activities around > 10 minutes, so now some users will logout from application if they should go to do something else (nothing to do with the application).

If my guess was right, this should be something to do with Connection Timeout setting on SQL Server or KeepAlive setting on Windows Server 2003.

Connection timeout is when some one try to connect sql server it will try until specified time then it will timeout...
Where as yours is connection closing which is alread esblished successfully...

When you run sp_who2 it will return all connections where as if pass the parameter 'active' then it will return only active connections...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

quote:Originally posted by MohammedU

Connection timeout is when some one try to connect sql server it will try until specified time then it will timeout...
Where as yours is connection closing which is alread esblished successfully...

When you run sp_who2 it will return all connections where as if pass the parameter 'active' then it will return only active connections...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

It's something that after succesfully connected and don't have any activities for certain period, and the server will automatically kick you out, i don't know what i should call it.

Hi, just let you know. Finally, I have found the answer for my questions on :
-http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21474
-http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21476
-http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21795

After i downgraded Windows 2003 R2 to Windows 2000 Advanced Server, all my problem on above threads are solved.

Windows 2003 R2 detected Dual-Core processor as 4 processors, While Windows 2000 Advanced Server detected as 2 processors. Something wrong with Windows 2003 R2?

Thanks a lot for all of your help.

Andy

Was HiperThreading enabled on the server?
It could be HyperThreading issue...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Hi Mohammed, how do i check whether HyperThreading enabled or not.

Regards,
Andy

You can check when you restart the computer in the BIOS...

In 2005 you can use DMV but not in 2000...

YOu can use the following if it is HP server...

-- HKEY_LOCAL_MACHINESYSTEMCompaqServerStd_EquipComponentCpu

set nocount on
-- create table #CpuNum (id int identity, name sysname)
create table #Cpu (name sysname)
declare @id int, @cpu Varchar(6), @CpuCnt int , @Lcpu int, @Pcpu int
select @id = 0, @cpu = 'Cpu'
while @id <=100
begin
select @cpu = @cpu + case when len(@id) = 1 then '00'+convert(Varchar,@id)
when len(@id) = 2 then '0' +convert(Varchar,@id)
when len(@id) = 3 then +convert(Varchar,@id)
end
-- insert into #CpuNum (name) select @cpu
insert into #Cpu
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SYSTEMCompaqServerStd_EquipComponentcpu',
@value_name = @cpu

select @id = @id + 1, @cpu = 'Cpu'
end
select @Pcpu = count(*) from #Cpu

create table #LCpuCnt
(
[Index] varchar(5),
[Name] varchar(20),
Internal_Value varchar(10),
Character_Value varchar(120)
)

insert into #LCpuCnt exec (' master.dbo.xp_msver ''ProcessorCount''')

select @Lcpu = Internal_Value from #LCpuCnt

if @Lcpu = @Pcpu select 'HT is not enabled'
if @Lcpu > @Pcpu select 'HT is enabled...' + 'Actual CPU countis :'+convert(Varchar,@Pcpu)+ '....Logical CPU count is:' +convert(Varchar,@Lcpu)

drop table #Cpu, #LCpuCnt


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Mohammed,

The result from your code is :
HT is enabled...Actual CPU countis :1....Logical CPU count is:2 (Windows 2000 Advanced Server + SQL Server 2000, which is the right one)

Why Windows 20003 detected as 4 processors? Or how do i turnoff HT?

Regards,
Andy