Wednesday, 12 May 2010

Are Transaction Rolled Back When CHECKDB Runs?

Ever come across a scary error in the SQL Server log when running CHECKDB against your databases. The error looks something like this:

1 transactions rolled back in database 'CHECKDB' (32). This is an informational message only. No user action is required.


Are transactions rolled back when CHECKDB runs?



No.



The key to this error is the last two sentences, Crash recovery has not been run on your production database. Since SQL Server 2005 an internal snapshot of the database is used for running integrity checks, this snapshot provides CHECKDB with a transitionally consistent snapshot of your your database. This error relates to the snapshot, crash recovery is run against the database snapshot so it is in a transactional consistent state for CHECKDB to run. Your production database is not affected.



Don’t believe me, well I don’t blame you, so checkout Paul Randal(Blog|Twitter) post(s) on the very subject.



Related Posts



SQL Server Integrity Checks

No comments:

Post a Comment

Featured post

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...