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:

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!

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

10 Comments on “SQL Server Checkpoint – The unknown behavior of Checkpoints in SQL Server 2012 – Be Aware!”

  1. Wouldn’t this mean that a checkpoint would never be issued for an active database thus leading to uncontrollable log growth? I know I have active 2012 databases that I am able to issue a checkpoint from a query window and that don’t have out of control growth of the transaction logs.

    Thanks.

  2. Hi Amit,

    Would turning on Indirect Checkpoints for the database in question change the behavior of this test? I would test it myself but I don’t have access to a SQL server at the moment.

  3. I am so sorry to tell you, that it might not have been the acctual behavior of previous versions, but surely it is the documented behavior.

    SQL Server is not expected to do any recovery and hardening until startup.

    Thats what I was told when attending SQ Server 6.5 and 7.0 classes.

  4. @Leif “but surely it is the documented behavior” – could you point me out where is it documented that checkpoint in 2012 requires X lock????

    “SQL Server is not expected to do any recovery and hardening until startup” – could you also point me out who told you this? or where have you read this?

  5. Yes Jamie, it sounds a little scary, I will run a few more tests in 1st week of August – short on time right now.

  6. Hi Amit,

    Thanks for sharing the info. Is it possible for you to provide the reference from MS? like URL of support call log id or acceptance of it as bug, etc.

    Senthil

  7. Hello Amit,

    Can you confirm that SQL Server 2012 requries an exclusive database lock in order to perform a checkpoint?

    This does not seem correct, as it would have to wait until there were no other connections to the database in order to acquire the lock.

    Thank you,

    Andrew Pruski

Leave a Reply

Your email address will not be published. Required fields are marked *