Sybase: Find long lasting connections

In order to find which connections have been open for a long time, you can use the following SELECT statement:

SELECT
   spid,
   sl.name as 'login',
   sd.name as 'database',
   loggedindatetime,
   hostname,
   program_name,
   ipaddr,
   srl.remoteusername as 'remotelogin',
   ss.srvname as 'remoteservername',
   ss.srvnetname as 'remoteservernetname'
FROM
   master..sysprocesses sp,
   master..syslogins sl,
   master..sysdatabases sd,
   master..sysremotelogins srl,
   master..sysservers ss
WHERE
   sp.suid>0
   AND datediff(day,loggedindatetime,getdate())>=1
   AND sl.suid = sp.suid
   AND sd.dbid = sp.dbid
   AND sl.suid *= srl.suid
   AND srl.remoteserverid *= ss.srvid
ORDER BY
   loggedindatetime

Here a few explanations:

  • sp.suid>0 filters the ASE processes which run with suid=0
  • datediff(day,loggedindatetime,getdate())>=1 filters in only processes which logged in date/time is more than 1 day old
  • if the login is a remote login, the remote login and remote server information will be filled. It will contain null otherwise

Leave a Reply

Your email address will not be published.