SQL Server Checkpoint – The unknown behavior of Checkpoints in SQL Server 2012 – Be Aware!

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:

create database sales
Go
 
use Sales
Go
 
--Turn on trace flag to flush the checkpoint output to error log:
 
DBCC TRACEON (3502, 3605,-1);

Get the database id of the database:

select DB_ID()

For me, the database id is 19.

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

CREATE TABLE dbo.Employee(
EmpID int PrimaryKey NOT NULL,
EmpName nvarchar (100) NOT NULL,
EmpEmail nvarchar (100) NOT NULL)

 
INSERT INTO dbo.Employee
values (1,N'Amit2',N'test@email.com')
 
 
INSERT INTO dbo.Employee
values(2,N'Amit2',N'test@email.com')
 
 
INSERT INTO dbo.Employee
values(3,N'Amit2',N'test@email.com')
 

INSERT INTO dbo.Employee
values(4,N'Amit2',N'test@email.com')

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

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:

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!