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

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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