SQL Server 2012 Logging of checkpoints

Question

I have upgraded a system from SQL Server 2008 R2 to SQL Server 2012 (in place upgrade).

After the upgrade I see messages like this in the log:

2012-05-05 11:59:21.81 spid15s     FlushCache: cleaned up 147155 bufs with 113034 writes in 96451 ms (avoided 14195 new dirty bufs) for db 6:0
2012-05-05 11:59:21.81 spid15s                 average throughput:  11.92 MB/sec, I/O saturation: 110102, context switches 191456
2012-05-05 11:59:21.81 spid15s                 last target outstanding: 66, avgWriteLatency 23

In earlier versions you had to enable trace flags to get information about checkpoints.
http://blogs.msdn.com/b/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx

I have no trace flags enabled.

So it looks like there is some new logging of slow checkpoints in SQL Server 2012. Does anyone know what the threshold is?

Ola Hallengren
http://ola.hallengren.com

Answer 1

hello Ola

eventually the following article may help you http://www.sqlserverfaq.net/2012/03/18/185/  (SQL 2012: Indirect Checkpoint Explained !!!)

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Dear Ola Hallengren,

This kind of message you will see incase if recovery_interval(min) value for a database is greater than 0. In your case at server level in SQL Server 2008 R2 the recovery_interval(min) value could be set to a value greater than the default value of 0 and hence after in-place upgrade you are seeing the messages as Indirect Checkpoint Feature of SQL Server 2012 is coming into effect.

For more information read the following Indirect Checkpoints Feature in SQL Server 2012 article.

Cheers,
Ashish Kumar Mehta

Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

Answer 3

BTW, checkpoint activity has been always exposed in PerfMon with a counter called something like "checkpoint, pages/sec". Using simple WMI this type of activity could be logged from vbscript or Powershell to file, database or any other format.

---

Henrik Fyhn http://repeatableread.blogspot.com/

Answer 4

Dear Ola Hallengren,

This kind of message you will see incase if recovery_interval(min) value for a database is greater than 0. In your case at server level in SQL Server 2008 R2 the recovery_interval(min) value could be set to a value greater than the default value of 0 and hence after in-place upgrade you are seeing the messages as Indirect Checkpoint Feature of SQL Server 2012 is coming into effect.

For more information read the following Indirect Checkpoints Feature in SQL Server 2012 article.

Cheers,
Ashish Kumar Mehta

Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

Indirect checkpoints are not enabled by default and they don't get enabled as a part of changing the recovery_interval(min) sp_configure option.  They are set per database with ALTER DATABASE, so I fail to see how this relates to Ola's question about why he is getting ERRORLOG entries about CHECKPOINT operations without the associated trace flag enabled in the engine.  You shouldn't post a response and immediately propose it as an answer to a post, especially when it's not in line technically with the problem being asked about.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 5

Hey Ola, 

Have you verified that the trace flag isn't set with DBCC TRACESTATUS?  I don't know of any changes in 2012 that would cause this logging to occur but after looking at a couple of my 2012 instances in my lab environment, I see the same messages so I will ping a couple of members of the product group to find out if there was something that would do this.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 6

I have verified with DBCC TRACESTATUS that no trace flag is set. I have also checked that recovery interval in sys.configurations is set to 0. I am not using the new indirect checkpoint feature. The version number is 11.0.2316.

This database has about 5000 - 10000 transactions (small insert and updates) per second.

The system seems to be working as good after the upgrade as it did before.

My guess is that Microsoft has introduced this as a warning for slow checkpoints (and that we should look into the disk system for the data file).

Thanks Jonathan for looking into this.

Ola Hallengren
http://ola.hallengren.com

Answer 7

I sent an email to Bob Dorr in product support to find out more about what would cause these messages to be logged in 2012, but he is currently out of the office according to his auto response, so I'll let you know when I hear back from him on this.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 8

Bob Dorr has clarified this in his blog now. Thank you for your help.
http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx

Ola Hallengren
http://ola.hallengren.com

Answer 9

Interesting discussion Ola/ Jonathan. Enjoyed reading this. Thanks Ola for raising question in the first place and follow up.

---

Regards,
Mark Broadbent.

Contact me through (twitter|blog)

---

Please click "Propose As Answer" if a post solves your problem
or "Vote As Helpful" if a post has been useful to you