I have found this really weird behavior thats kind of haunting me for more than a week.
I have a .NET process that writes records to a table(this table is huge, with about 300 million records) in two transactions using the classic Data Adapter and dataset.
(Using Visual Studio 2008, VB.NET, Console Application, .NET framework 3.5 SP1 and SQL Server 2008 Cluster with 32GB of RAM on server.)
tran1 = conn.beginTransaction()
. In the first, it inserts around 20,000 rows and commits and in the second it inserts around 6,000 and commits.
When I run this process stand alone, it works fine. I see all the records in the table.
I was trying to stress test this to see how many instances are supported.
When I went to 25 instances of this process(Each writing 20k and 6k records in two transactions), I saw something strange. Out of the 25 instances, for 4 instances, the transaction writing 20k records was rolled back mysteriously. I did not get any errors(which
I was expecting). Worse, as these 25 instances were running, I was running a select from table(with nolock), and the record count was growing for all the records inserted into those 25*2 transactions. However when they all ended(without any exception/error)
and I run the same select as I was running all the time, the records for around 4 transactions which could be seen in the query before, have now disappeared without a trace!!!.
1. Is there a limit to the number of record inserts SQL server 2008 can handle.
2. Even if there is, how come I have issued the tran1.commit(), code doesn't give an exception, but still doesn't stay in the table.
3. Where did the records which I can see in the select (with nolock) while the processes are running disappear after the processes ended?
4. Why does only the transaction inserting 20k records roll back and not the one with 6k records?
5. Is there some place where it logs this error, so that I can retry which were rolled back?
I could run 20 instances without a problem, but saw this roll back behaviour when I increased the load to 25 instances.
I am sure this really requires some deep SQL Server DB understanding and would greatly appreciate if someone can help me with this.