Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
Recently, a DBA said that the Log files were growing bigger than the data file size. During the discussion, he mentioned using CHECKPOINT to solve this problem. In relation to this, what does CHECKPOINT do inside SQL Server? Is the behavior change based on Database recovery models?
Notes of Vinod Kumar
Database CHECKPOINTS are interesting. Read more about them. And, as you read more and more, you will surely stumble upon these related questions: What are the correlations of RECOVERY models and their effect? Do dirty records also force flushed to data file during CHECKPOINT? As a DBA, go ahead and play around with this; you’ll find out this is an interesting exercise, trust me.
Notes of Pinal Dave
CHECKPOINT is very exciting concept. I have seen how this function is used, and mis-used. The only advice I have for everyone who is not familiar with this command is that NEVER experiment with it on your production server. In one of the recent technical presentations, I noticed that a few attendees were using CHECKPOINT incorrectly. When I asked a few follow up questions, I learned that their understanding of the dirty records was incorrect. All the advanced concepts require a solid understanding of the basic concepts. Without the basic understanding one person can never master the subject.
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#137
Target Recovery Time of a Database – Advance Option in SQL Server 2012
Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (http://blog.sqlauthority.com)