Hi Friends,

Probably, you would not be aware of the new SQL Server checkpoint behavior in SQL Server 2012. You might be or might not be surprised, but you certainly need to be aware of this.

Start SSMS, in a new query window, create a new database:

Get the database id of the database:

For me, the database id is 19.

Create a table and insert a few records (implicit transaction):

Shutdown SQL Server from Object Explorer while the query window is open:

1_SQL_Server_The_unknown_behavior_of_Checkpoints_in_SQL_Server_2012_Be_Aware

Note that this is a clean shutdown (equivalent to SHUTDOWN statement).

Navigate to the error log folder, for me its here: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012SP1\MSSQL\Log

Open the errorlog file in notepad and you will observe that checkpoint is skipped for this new database:

2_SQL_Server_The_unknown_behavior_of_Checkpoints_in_SQL_Server_2012_Be_Aware

Checkpoint until database id 18 is called, but is not called for database id 19. But more importantly, it does not even say that checkpoint is skipped for database id 19. Which means; the four records that were inserted are still in log file and have not been hardened on the data file even after a clean shutdown. This also means that if your log file at this stage gets corrupted, you will lose the data (if you want to give this a try, delete the log file, start the service again, the sales database goes into recovery pending state, take the database into emergency mode and single user mode, rebuild the log using DBCC CHECKDB (‘sales’, repair_allow_data_loss), the database is online, set back to multi user mode, check for the table – but it won’t be there).

This is not the case in earlier versions.

Now, the main question, why does it happen this way? This ‘strange/surprising’ behavior (if I am allowed to call it that way) happens because in SQL Server 2012, it requires X lock on the database to issue checkpoint. This means that if you have even a single user connected to the database, checkpoint will not be called (since database S lock is taken). SQL Server Product Team and my friend Karthick PK (SQL Server Support Team Lead) has confirmed this.

But IMHO, the error log should say the checkpoint is skipped for that database.

Most importantly, this is not documented. I have reported this to Microsoft SQL Server product team and they have responded that they will look into this and evaluate any logic changes in future release.

For the time being, just be aware!