We are getting error messages from our SQL-server when we run a stored procedure to increase a counter. This SP is executed many times for different entities but when executed for a shipment-number from GLS we sometimes get:

"Cannot create new transaction because capacity was exceeded"

The SP is executed with a separate username to exclude it from the transaction(s) the user is in.

What causes this error message?

  • 1
    Are you calling this SP from an external app? – McNets Oct 21 at 14:51
  • Yes. The SP is called from a XPA-application but with a different logon. Also the SP is opening a separate transaction and closes it after the job is done. – Florian Oct 22 at 13:02

What Edition of SQL Server are you using?

Judging from what I see out there, it seems that This is an ADO / OLEDB problem not a SQL-Server one. Even though you are exceeding the transaction capacity of SQL Server, it is actually the application not closing the connection the root problem.

One suggestion I saw is to run SQL profiler, and look into how many connections are opened in your application (that would be in General Statistics - User Connections)

Another suggestion is to look into the possibility of an endless loop creating a nested transaction. According to this document, SQL Server has a practical limit of 4,294,967,296 nested transactions. That number is never reached when doing things right, but if you are stuck on an endless loop that creates a transaction inside another one, this could cause some errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.