3 Replies - 2223 Views - Last Post: 03 March 2011 - 11:28 AM Rate Topic: -----

#1 callahan09  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 61
  • Joined: 13-April 10

Cannot create a new transaction (previous transaction did not close pr

Posted 01 March 2011 - 04:31 PM

Hello, I really hope someone can help here because I've been racking my brain for 2 and a half hours now about this particular issue and I have been working straight without a break for 11 hours today. I'm losing my mind trying to cope with this issue and I'm going home right after I type this question, so if I don't respond to follow-up inquiries about my issue until as late as tomorrow morning, I apologize.

So, the issue: I've got this sync application that updates changes in one database to a SQL Server 2008 R2 database. It uses a transaction and executes about 10,000 insert SQL statements before doing the Commit / Rollback (on error).

This worked flawlessly until one run-through my computer crashed in the middle, so the transaction did not Commit or Rollback.

Ever since then, I get this error when my program gets to the "BeginTransaction" statement (using OleDB via C# to do all SQL Server interactions):

"Cannot create new transaction because capacity was exceeded."

I have tried everything but rebooting the sql server (which I will be doing in the middle of the night when it's no longer in use by others). I have deleted the database and recreated it, I have recreated it under a different name, I have detached and brought it back online, I have restored backups, I have rebooted my client machine which attempted to do the transaction to begin with.

I have a feeling rebooting the server won't help, but I'm going to try it anyway. But assuming it doesn't help, what else is left? What else could be the problem? What do I do in a situation like this? I can't run my sync anymore because it fails when trying to create the new transaction, so... Is there some way to go in and kill off any transactions that weren't properly disposed of in the past? Is that what might be causing the issue and could that be a solution if it's at all possible?

Please help!

Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Cannot create a new transaction (previous transaction did not close pr

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4318
  • View blog
  • Posts: 12,100
  • Joined: 18-April 07

Re: Cannot create a new transaction (previous transaction did not close pr

Posted 01 March 2011 - 07:15 PM

I believe rebooting the server will indeed fix the problem. From what I can tell of the error, it is usually caused by too many recordsets being open on the same connection. In the future though you should probably break the transactions down into smaller chunks if you can. Instead of like 10,000 inserts break them into 10 1,000 inserts or something.

When you crashed you probably left a recordset in limbo and open and now when you are trying to run a new transaction it is having a problem. A server reboot should clear that open transaction.

:)
Was This Post Helpful? 1
  • +
  • -

#3 callahan09  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 61
  • Joined: 13-April 10

Re: Cannot create a new transaction (previous transaction did not close pr

Posted 02 March 2011 - 06:51 AM

Hi Martyr2, I appreciate your comment, but the reboot of the server did not resolve my issue.

Any other ideas, then?
Was This Post Helpful? 0
  • +
  • -

#4 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Cannot create a new transaction (previous transaction did not close pr

Posted 03 March 2011 - 11:28 AM

have you tried typing sp_who2 to see if there's a lock somewhere and then kill the process if there is one?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1