Solved

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

Posted on 2014-10-08
10
304 Views
Last Modified: 2014-10-20
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.
0
Comment
Question by:gcook17
  • 5
  • 4
10 Comments
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 250 total points
ID: 40369159
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.
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 250 total points
ID: 40369495
Check your code and be sure that you close each connection after opening and using it !!!

hth

Mike
0
 

Accepted Solution

by:
gcook17 earned 0 total points
ID: 40371538
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?
0
 

Author Comment

by:gcook17
ID: 40378101
Even with application not running connections continue to accumulate in SQL Server. Why? How could it be determined where these are coming from?
0
 

Author Closing Comment

by:gcook17
ID: 40391498
Closing and disposing all command objects seemed to help but connections still accumulate.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

by:DcpKing
ID: 40391814
"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

Mike
0
 

Author Comment

by:gcook17
ID: 40391932
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40393485
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.

hth

Mike
0
 

Author Comment

by:gcook17
ID: 40393523
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.
DB-Connections.jpg
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40393592
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
select @@MAX_CONNECTIONS
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

hth

Mike
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now