Max. number of transaction

[martin_vista]

Hi,

Does anyone know what the maximum number of commads is that I can add to a transaction object? (in System.Data.SqlClient)

I googled, but couldn't find it..

 

[cjard]

In oracle, this is a variable that the DBA can alter.

I suggest you try it.. write a loop that never ends, and it just starts a transaction and then performed INSERT queries without cease into one table until it falls over

Keep a count, of course

 

[martin_vista]

Ok,
I wrote a little program that adds commands to a transaction in a infinite loop.
Added a counter and used a try-catch to give me a message when ever the system would decide there are too many commands.

Guess what happenend... looking at it I wonder why I didn't figure out before...

The program runs ok, I stopped it myself after 15 minutes.
It was not consuming too much of my processor, nor was it consuming extremely much memory.
What happened is that the log file of my SQL Server was growing. ... transactionlog ....

So I think the limitation of the number of transactions is connected to the limitations set for your transaction log files. As far as I now a transaction log can grow unlimited ( it can grow bigger than your database....) So the limitation here would be the empty space left on your filesystem.


Thanks.

 

[cjard]

Do note that this could cause some pretty horrific bottlenecks in a multiuser system, while the DB works out which original rows a user is asking for..

Just something to bear in mind...