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
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 81

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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
 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

717 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