June 12, 2008 at 9:48 am
It appears that we have a problem with a long rollback. The transaction that was running was doing a join between two tables to insert records into another table. I know for a fact that the join returns count(*)=0. Since the count is 0 shouldn't the rollback be very quick -- i think it should because no rows were written to the table.
The insert statement was running for about 8 hours before we killed it.
Sample qry:
insert into table1
select * from table2 inner join table3 on field1=field1a and field2=field2a
where amt=9999
table2 has 7.8 million rows
table3 has 305 million rows
Thanks
June 12, 2008 at 10:46 am
The rollback just finished, it took about 3.5 hours to complete.
June 12, 2008 at 1:23 pm
Just and FYI if you are doing a insert and its taking 8 hours and you query the table it will return zero rows because it didnt commit the transactions. It still has to rollback any transactions that it was going to commit.
June 12, 2008 at 1:34 pm
But the number of rows to be inserted was 0, so the question is why the rollback ran for so many hours. I can understand that the join might have not been effective, but when the job was killed it should rollback quickly as there were no entries in the t-log. Am I correct?
June 12, 2008 at 2:32 pm
Maybe the join was spooling a large amount of data to tempdb ?
Did you check the execution plan ?
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply