VB.Net 2005 ADO.Net, SQL Server 2008 R2: Error:" Cannot create new transaction because capacity was exceeded."

VB.Net 2005 windows forms application using ADO.Net. Linked to a SQL Server 2008 R2 database with approximately 2,000,000 records. Table throwing error has approximately 48000 records. Attempting to add a record to this table sometimes results in error: "Cannot create new transaction because capacity was exceeded."

Usually after three attempts the error disappears and record is added successfully. Please help me solve this issue that seems to only occur on this table. Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
too many connections?
After you opened a single recordset, execute line "Select @@Connections" in Query Analyzer. Does your # of connection keep on increasing?

you could have encountered an OLE DB restriction. Try to trace using SQL Server profiler what actions lead to opening many connection. Probably it will be enough to close unnecessary datasets in your applications.
Check your code and be sure that you close each connection after opening and using it !!!


gcook17Author Commented:
Thank you for your comments so far.

I am reusing one connection. Command objects are now being disposed and this seemed to help however when SQL Server 2008 R2 is opened there are 27 connections. When .Net application connects it adds 8 more immediately. Opening some screens in this MDI Windows Forms database front end do not add to the connection count others ad 18+ connections. Once the application is closed all connections persist if queried in SQL Server "SELECT @@Connections". These connections do not seem to ever go away unless the SQL server is restarted. There are 5 database on this server but only one is connected to application. There is only one place in the application that can open a connection and it is run once at start up and never executes again. Where are all these connections coming from?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

gcook17Author Commented:
Even with application not running connections continue to accumulate in SQL Server. Why? How could it be determined where these are coming from?
gcook17Author Commented:
Closing and disposing all command objects seemed to help but connections still accumulate.
"with application not running connections continue to accumulate"

Well, something must be creating the connections! I know that this may sound obvious but keep trying to find the application that's adding them. It's obviously not the application that was failing previously, but there's a cause somewhere. Good luck

gcook17Author Commented:
Yes there are approximately 20 databases on this server but all are used for testing the application in question. With the application not running connections continue to accumulate. Not sure how to determine what other code, software or ?? is starting these phantom connections.
Are the connections all to one database, can you tell?  I'd suggest "turning off" all the test-only databases and seeing if anything crashes - then you might have found your culprit.


gcook17Author Commented:
Thank you for your comment. There are 21 databases on this server and it is actually the server connections that keep incrementing. The 21 databases hold a count of 21 and there is one active database connected to a vb.net front-end connected to one of the databases. See attached.
That doesn't seem to be too crazy - I have a 2012 instance in a VM here on my laptop and I spun it up and, with about 15 databases, this code:
select sp.dbid as 'DB ID', sd.name as 'DB Name', count(sp.dbid) as 'Connections' 
from sys.sysprocesses sp
inner join sys.sysdatabases sd	on sd.dbid = sp.dbid
group by sp.dbid, sd.name

Open in new window

gave me
DB ID  DB Name                                                                                                                          Connections
------ -------------------------------------------------------------------------------------------------------------------------------- -----------
1      master                                                                                                                           25
4      msdb                                                                                                                             2
5      ReportServer$UBUNTU_2012                                                                                                         1

Open in new window

How on earth master retains 25 connections when doing absolutely nothing I have no idea. However, my little instance gave a value of 347 for connections.

To see your limit try
which, for me, is 32767.

Although they may look alarming, they may not be - look at the MS docs here.

As for your crashes, I'd suggest running my bit of code above in a look every minute, say, writing the results into a table. You'll see the connections rise. Then you'll see which db for sure!  I suspect that it isn't connections that's killing you, but logins - something in that application may be logging in all the time, and not realising that it already is logged in, or something insanely simple like that.

Good luck


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.