Όπως είναι γνωρίζουμε στον SQL Server υπάρχει μια διαδικασία  η οποία σκοπό έχει να μεταφέρει από την Buffer Cache τις αλλαγμένες σελίδες στο δίσκο, γίνεται σε όλες τις βάσεις εκτός από την tempdb (αν και αυτό δεν είναι και τόσο αλήθεια αλλά δεν θα το αναλύσουμε εδώ). Σκοπός του είναι να είναι όσο το δυνατό πιο ενημερωμένα τα data files με τις αλλαγές είναι γραμμένες στο log.

Αυτό μπορεί να γίνει είτε εκτελώντας την εντολή CHECKPOINT είτε το κάνει o SQL Server αυτόματα. Το αυτόματο μπορεί να ορίστει αν το επιθυμούμε και αυτό γίνεται ορίζοντας το Recovery Interval στο SQL Server. H default τιμή είναι μηδέν που σημαίνει ότι ο SQL Server αποφασίζει για το πότε θα την κάνει αυτή την διαδικασία.

Πρόσφατα άκουσα ότι αυτή αν δεν ορίσεις τιμή γίνεται κάθε 2 sec. Αυτό με έβαλε σε σκέψεις και αποφάσισα να το ψάξω ενδελεχώς.

Σύμφωνα με τα BOL http://msdn.microsoft.com/en-us/library/ms189573.aspx στην παράγραφο για τα Automatic Checkpoints αναφέρει

The SQL Server Database Engine generates automatic checkpoints. The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. Automatic checkpoints can also occur frequently if lots of data is modified.

Αυτό όμως δεν μου έφτασε και άρχισα να κοιτάζω περισσότερα. Έφτασα σε ένα σημείο να ρωτήσω και ανθρώπους που τον έχουν φτιάξει (δεν αναφέρω τα ονόματα τους για μην θεωρηθεί ότι πουλάω μούρη ότι μιλάω μαζί τους). Τελικά οι ερωτήσεις μου και οι αναζητήσεις μου είχαν αποτέλεσμα και ανακάλυψα ότι μπορείς να χρησιμοποιήσεις τα trace flags 3502 και 3605  τα οποία γράφουν μέσα στο error log το κάθε πότε γίνεται checkpoint.

Επίσης είναι αρκετά εύκολο να το δεις κάνοντας monitor τον Buffer Manager\Checkpoint Pages per Sec perfmon counter.

Με ποιά όμως λογική γίνεται trigger η αυτόματη διαδικασία? Η απάντηση δια στόματος του Paul Randal είναι η παρακάτω

A checkpoint is triggered automatically under a variety of conditions. The most common condition is that enough transaction log has been generated that SQL Server estimates that if the server was to crash, it would take about one minute for crash recovery to complete. This is calculated based on the number of log records that have been generated since the last checkpoint and is known as the recovery interval. The next most common condition that triggers a checkpoint is when the log becomes 70% full.

@antonch