CODEWITHSUNDEEP
c#sql-serversqlconnectionsqlconnection.close
stckvrflw
stckvrflw

Reputation: 1519

What disadvantages are there for leaving an SQL Connection open?

This seems to be a simple question, but I wonder the disadvantages of not calling the "close()" function.

Upvotes: 8

Views: 2616

Answers (6)

John Sansom
John Sansom

Reputation: 41899

Each and every connection to SQL Server requires memory allocation.

So the more connections you have open the more memory that is being used and held, that could potentially be put to better use.

If you want to know just how much memory is used by connections in SQL Server, take a look at the following reference.

Memory Used by SQL Server Objects

Upvotes: 1

Oded
Oded

Reputation: 499392

Apart from exhausting the connection pool (as most answers so far have been), you are in danger of locking data.

If you are reading or writing to a table, some locking semantics will cause certain rows to be locked to other connections. This is especially true if you have any open transaction on the connection.

Reads and writes can then fail and the application will throw exceptions all over the place.

In short, always close the connection.

Upvotes: 7

Justin Wignall
Justin Wignall

Reputation: 3510

Quick simple answer is that the sooner you close, the sooner the connection can be re-used by the connection pool.

Upvotes: 0

Thanos Papathanasiou
Thanos Papathanasiou

Reputation: 952

a connection to the database server is open. suppose you have 100s of programs hitting the same server...

you should also dispose it

Upvotes: 0

bleeeah
bleeeah

Reputation: 3604

The connection pool will fill up and any new connections will time out waiting for a new connection from the pool.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Sooner or later, you'll run into the "The Maximum Connection Limit Has Been Reached" error. I'd call that a major disadvantage.

Upvotes: 10

Related Questions