Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3645
  • Last Modified:

SQL SERVER SESSION TIMEOUT SETTING - Front End Form Corruption Problem


We have a legacy financial application that was ported a few years back from Access to SQL Server 2000.  Users still access the server through a MS Access form front end.  We have an ongoing problem in which the user  access form continually gets corrupted and users have to redownload this form from the server.  I suspect but don't have any real proof that form is getting corrupted because the majority of our users who access the system (directly from their desktop or via terminal server) never logout and usually leave the application up and running for many hours at a time.  

I am looking for a way to automatically log the users out and terminate the connection to the database if the connection has been idle for  a certain period.  

If there are any other suggestions as to why this issue maybe occurring it would be greatly appreciated.

 Thanks in advance.
1 Solution
As far as I know you can not configure a "Connection timeout" server side.
But I have used a script like below in the past to get rid of Access connections.

print 'Begin checking kill sequence...'
declare @DelayInMinutes int, @spid int, @ApplicationMask varchar(50), @sql varchar(15)
select @DelayInMinutes = 10, @ApplicationMask = '%Access%'
while 1 =1
      while exists(
      select * from master..sysprocesses
      where status = 'sleeping'
        and spid > 50
        and last_batch < dateadd(minute, -@DelayInMinutes, current_timestamp)
        and [program_name] like @ApplicationMask
            select top 1 @spid = spid
            from master..sysprocesses
            where status = 'sleeping' and spid > 50
              and last_batch < dateadd(minute, -@DelayInMinutes, current_timestamp)
            and [program_name] like @ApplicationMask
            print 'Killing spid ' + convert(varchar(10), @spid) + '...'
            select @sql = 'kill ' + convert(varchar(10), @spid) + ''
            exec (@sql)
      print 'Waiting for 5 minutes'
      waitfor delay '00:00:05'


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now