• The way I read the article, the question was whether rolling back a transaction would also roll back any writes to the snapshot. This is quite different from suggesting that the act of rolling back a transaction results in any additional writes to the snapshot. I have re-read the article, and I still think it is reasonably clear on that point.

    Article


    We all expect the snapshot database files will get updated when there is a change in source database page. However, what happens if a transaction gets rolled back? Effectively no changes were made to the data page in the source database and hence no changes should be made to the snapshot database files. Is that correct? The answer is NO.

    The problematic statement there is "no changes were made to the data page in the source database and hence no changes should be made to the snapshot database files". As Paul mentions, although the row data will be the same after a rollback, changes are made to the page header and so the page is physically different after the rolled back transaction, and so must remain in the snapshot.

    There might be an expectation that rolling back a transaction would also roll back any writes to the snapshot. The article shows that this is not the case - a result which may not in fact be obvious to all.

    It might also be surprising that all brand new allocations are also written to the snapshot - again, even if the transaction is rolled back. So, a structure which is created entirely within a transaction will grow the snapshot, even though it didn't exist at all before or after the transaction!

    Snapshots are conceptually very simple, but there are many interesting features, which are not self-evident and nor are they documented. Bob Dorr of the CSS SQL Escalation Services team has a couple of interesting blog posts on the subject:

    http://blogs.msdn.com/psssql/archive/2008/02/07/how-it-works-sql-server-2005-database-snapshots-replica.aspx

    http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx