The series of activities I have been writing on CHECKDB, I just dont seem to stop exploring interesting things about this command. It is so powerful that I just love it. In a session with MS Internal IT today I was talking about how CHECKDB is done as an ONLINE session and we changed to this mode since the SQL Server 2005+ days. So now CHECKDB is run of an internal snapshot database. This snapshot is similar in some respects as if you were to create a database snapshot using the CREATE DATABASE command. The snapshot created by CHECKDB is not visible to users in any form. When you query catalog views or system tables, there will be no reference associated to this snapshot.
Having said all this, the world was way different in the SQL Server 2000 days. For academic purposes, the CHECKDB used to perform expensive log scans to bring individual pages to a consistent state to perform analysis on the pages and generate facts to check the consistency. Also there was expensive locking involved to keep the analyzed data consistent. All of this is done on the live database on which data modification is happening. So now you get the fact on how CHECKDB has evolved over a period of time.
Coming back to the SQL 2005+ implementation, this internal snapshot are a transient replica of the database and is not a persistent replica like what we create using CREATE DATABASE with SNAPSHOT. So once the CHECKDB operation completes, we gracefully delete this transient database.
Now that we are on this interesting point, let me give you a behavior that is interesting that I was discussing with Pinal Dave today over lunch. Take this simple scenario, we have an “Open transaction” when the CHECKDB gets initialized. Just like below –
Insert into temp1 values (1)
Now when I run the CHECKDB on this database (on a different window), when this above transaction is OPEN, there is an interesting message on the SQL Server Errorlogs that is worth noting.
Interestingly, this seems to have rolled-back our transaction without our consent? No way, dont panic. This has not rolled-back our database transaction, but since CHECKDB needs to run against a consistent database, it rolled-back our transaction on the “Transient Replica” aka internal snapshot. To double check this behavior, issue COMMIT back on our original query window (with INSERT stmt) and you can see the data with value of “1” has indeed got committed into the database :).
More interesting trivia on SQL Server coming your way … Thanks and feel free to pass your comments.
This entry was posted on Tuesday, May 18th, 2010 at 13:30 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.