In the course of work about 1000 connections to base accumulate. Servers start to work worse. How to make what used old connections were torn?

  1. The database is connected to the site via ODBC. More than 10,000 visitors per day. To the site in C #, using the using rules () {}, the MSSQL database is connected via ODBC. At the same time, on the MSSQL server, more than 400 connections and, accordingly, open sockets freeze, which are not used clearly or not.

  2. The database is connected via ADO + ODBC from MSSQL to infox. There is even more fun, 1000 connections can be opened per day, and in the process of work it is often around 7000. It seems that ODBC does not have a garbage collector or it ignores the close / release command of the interfaces working with the DBMS.

Connect with MSSQL Provider = SQLOLEDB; Data Source = 10.0.0.79; Initial Catalog = mybase; User ID = user; Password = passw; Connect Timeout = 900; Command Timeout = 900

The connection with Informix is ​​configured via alias and SetNet32. Server = myserv HostName = 10.0.0.200 Service = Turbo protocol = olsoctcp System on DBMS servers - windows-2008 server 64bit.

  • Open connections are normal consequences of connection pooling. Servers do not start "worse" from them. The alternative is to open a physical connection to the server each time, which is rather slow. You should look for the real cause of "deterioration" if it somehow manifests. - PashaPash ♦

2 answers 2

I did not do this myself, but in general the connection string has the following parameters:

  • Connection pooling
  • MinPoolSize
  • MaxPoolSize

In theory, these parameters should manage the connection pool and limit the number of active connections to the database from one connection string. You can try to explore the recommendations for the installation of these parameters and find the appropriate values.

    I wrote quite a few small downloads, and as a rule they were one-time programs, and almost never did Close. It usually took place on its own (upon completion of the program’s work), and there were no problems with it.

    EXEC @hr = sp_OACreate 'ADODB.Connection', @ado OUT, 1 if @hr=0 exec @hr = sp_OAMethod @ado, 'Open', NULL, 'MyConn' if @hr=0 EXEC @hr = sp_OAMethod @ado, 'Execute', @rs OUT, @sql set @eof = 1 if @hr=0 exec @hr = sp_OAMethod @rs, 'EOF', @eof out -- код if @rs is not null exec sp_OAMethod @rs, 'Close' -- Добавил if @rs is not null exec sp_OADestroy @rs if @db is not null exec sp_OAMethod @db, 'Close' -- Добавил if @db is not null exec sp_OADestroy @db if @ado is not null exec sp_OAMethod @db, 'Close' -- Добавил if @ado is not null exec sp_OADestroy @ado

    Since the script works through the service (because it is executed by the MSSQL server), the service for some reason does not close any of the connections. Adding Close the connection is closed.