SqlTransaction rollback error

[Doug]

Hi,

I've read through some posts on this error but none seem to exemplify
what I had happen to me and wanted to see if anyone else has seen this.
I know a lot of people like to see code, it's going to be hard to show
you code examples as I'm using a lot of our common code (which is
large) and some of that wraps Microsoft's application blocks. I'll try
to explain it as best I can though:

Basically, I have an app that is doing some writing to the database
(don't they all? ) and is using our common data code for committing
and rolling back transactions. The data code has a class that I'm
using that keeps a connection object open.

In testing, my app took a very short period of time to run - but in
production it is taking a much longer time (another common problem I'm
sure). However, it took so long that I ended up getting this error:

"This SqlTransaction has completed; it is no longer usable."

My thought is that since I kept a connection object open and that the
application took waaaayyy longer to run through than it should (over 8
hours), that the connection was lost and that is why I got this error
because it couldn't use a transaction on a closed connection. Does
that sound like a reasonable explanation of why I might get an error
like this?

Note: By the way, I am fixing my performance problem - it's outside
the scope of this question though.

 

[Cor Ligthert [MVP]]

yes

 

[Miha Markic [MVP C#]]

You mean you are running non-stop operations against a database for 8 hours?
BTW, if you don't commit a transaction and connection is lost you don't need
to rollback.
However, it might be also that you've called commit (or rollback) on
transaction before doing this last rollback and getting that error.

 

[Doug]

You mean you are running non-stop operations against a database for 8 hours?

Well, I wasn't planning on doing it for 8 hours! During my
development testing, it took about 5 minutes! And this was even doing
a very large number of transactions on dev so that I could try to
predict some load performance. I would have never anticipated 5
minutes translating to 8 hours when going to production (I think we
have some serious SQL issues as well but am not sure). That's why I
mentioned that I am looking into coming up with some performance tweaks
to the process.

 

[Miha Markic [MVP C#]]

That kind of change (5mins->8hrs) just screams for finding bottleneck

 

[Doug]

That kind of change (5mins->8hrs) just screams for finding bottleneck

Yeah, tell me about it. I'm convinced there is an underlying SQL issue
on our production SQL Server box but my particular level of expertise
is not in that area so I'm not sure how to approach it. I am not sure
how anyone can do any good performance testing when a 5 minute test on
a development sql server translates to 8 hours on production. There
was definetely a performance issue on my part - but when I fixed it, it
brought the 5 minutes on development down to 5 seconds. However, who
knows what 5 seconds on dev translates to on production (it's not moved
yet)!

 

[Miha Markic [MVP C#]]

Hi Doug,

Is there a difference in amount of data?
Did you try looking at query plan?
Are database structures different?

 

[Doug]

Is there a difference in amount of data?
No. I took the data from production that took over 8 hours to go
through and ran it on development in order to see what kind of
performance difference there is. That's where I noticed that 8 hours
took 5 minutes on development. Then I fixed my performance issue,
retested with the same data and it dropped from 5 minutes to 5 seconds.

Did you try looking at query plan?

Yes. All query plans were reviewed before and after moving to
production. The query that is taking a long time is a very simple
select (the performance issue was that I was calling it more times than
I needed to).

Are database structures different?

No. The database was brand new and moved in when my entire system
moved into production.

As a department, we do an extraordinary amount of work inside of sql
(large number of DTS's, jobs, etc). I've only been here a year, but
it's way more than what I remember doing at a previous company. I
believe that may have something to do with the issue I saw as well,
because there is an extensive amount of what I would personally deem
"unnecessary" traffic inside SQL (I'd rather see some of that move out
into applications instead of having it all inside SQL). However, I
have not been successful in getting that message across, we have a
larger number of SQL developers than application ones and I have not
been able to convince them of this unfortunately.