While analyzing Process/Session Information in SQL Server Current Activity over the last several months on our ERP's SQL Server I have noticed that the number of sessions open starts at a very low number after a system reboot (150), then slowly grows as users login and system activity increases. This seems very normal, except that over the next few weeks the number slowly increases (3500) until we reboot.

After looking at the sessions closely many of them have login times and last batch times that are the same, which tells me that a connection was made, but nothing has really happened since. For example, many of the sessions were opened 5 days ago and the last batch run by these sessions were 5 days ago too. Most of them are have a last SQL command batch ran as “sp_cursorclose;1”, ”sp_cursorunprepare;1”, or ”sp_cursorexecute;1”.

Performance definitely degrades over time as the number of sessions increases, until we reboot. Also, you would expect to see this number of sessions decrease during slow times, but this does not occur. Could something be mistakenly leaving connections open?

SQL Server 2000 sp3
Windows Server 2000 sp3
ERP - PeopleSoft EnterpriseOne XE