Does Sql make implicit rollback if my rollback timeout?

Question

I have a dotnet applicaton that executes a set of insert,update,delete statements in transactionaly manner

The code is like this

try
{
mytrans = mycon.begintransaction();
//execute sql statements
mytrans.commit();
}
catch(Exception)
{
mytrans.rollback();
}

The problem is that sometimes we faced timeout exceptions in rollback and I found that the database size (mdf file) increased!!! So it means Sql will not make implicit rollback? if so how can I recover from this error and go to the original state???

Answer 1

Any SQL that may have executed will not have yet been committed. If your Rollback for some reason timeouts that won't result in a commit. Hence eventually the DB will realise its all gone pearshaped and will discard the changes.

An increase in MDF size is not an indication that the transaction has been committed. However the results of the transaction need to be put somewhere. The committing of transaction should require the smallest change possible in the DB. Hence pages may be allocated and data written and then on commit just a few other bits pointing in all the right places are tweaked.

If there is a rollback those last few bits are not tweaked and those allocated pages simply become free pages to be used for other things. You can't expect the DB to just shrink again.

Answer 2

The fundamental concept of transactions demands that transactions that are not committed do not affect the state of the database.

File size does not mean anything. RDBMS data structures are far more complex than simply adding a line to a file - they include logs and indices, so the file can grow and shrink quite independently from the amount of data in the DB.

Answer 3

Page slits can survive rollbacks:

http://sqlfool.com/2009/04/page-splitting-rollbacks/

Answer 4

Once a Rollback has started it MUST complete. Whether you are still connected or not, SQL Server will still complete the Rollback. Failure to complete a rollback leaves your database transactionaly corrupt and in need of recovery.