Share via

How It Works: When is the FlushCache message added to SQL Server Error Log?

  • Article

FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag (3504) is enabled.

2012-05-30 02:01:56.31 spid14s FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0

2012-05-30 02:01:56.31 spid14s average throughput: 24.49 MB/sec, I/O saturation: 68365, context switches 80348
2012-05-30 02:01:56.31 spid14s last target outstanding: 1560, avgWriteLatency 15

Prior to SQL Server 2012 the trace flag had to be enabled in order to output the information to the SQL Server error log. (Trace flag was the only way to obtain the output. )

SQL Server 2012 adds an additional condition (is long checkpoint) to the logic. If the trace flag is enabled or the checkpoint 'TRUE == IsLong' the message is added to the SQL Server error log.
 
Is Long Checkpoint: A long checkpoint is defined as a 'FlushCache / checkpoint' operation on a database that has exceeded the configured recovery interval.
 
If your server does not have the trace flag enabled (use dbcc tracestatus(-1) to check) the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval. If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.
 
Not meeting the recovery interval target means that recovery from a crash could exceeded operational goals.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    June 03, 2012
    Certaines de mes mises a Jours restes bloquées. Merci de m'aider

  • Anonymous
    December 26, 2012
    What does this mean when the target recovery time is 0 and the trace flag is not enabled? We're seeing it regularly in one of our databases that was moved to SQL 2012 via detach/attach.

  • Anonymous
    January 17, 2013
    Does this means we have to increase frequency of checkpoint occurence? Or some other resource needs handling

  • Anonymous
    February 28, 2013
    We recently moved to 2012 on a huge 64 processor machine - super fast and we are experiencing the dreaded "SQL Server has encountered NNNN occurrence(s) of I/O requests taking longer than 15 seconds to complete on file:..."  The new Indirect checkpoints are supposed to "smooth out" I/O spikes, but is it possible for them to have a negative effect on I/O?  Some of these 833 messages are associated with the Flushcache occurence in the Errorlogs.  

  • Anonymous
    April 24, 2013
    Yes, this discribes my problem, but what do I do about it? SQL2 4/24/2013 2:07 spid15s Unknown last target outstanding: 59600<c/> avgWriteLatency 0 4/24/2013 2:07 spid15s Unknown average throughput:   1.62 MB/sec<c/> I/O saturation: 10346<c/> context switches 23568 4/24/2013 2:07 spid15s Unknown FlushCache: cleaned up 78146 bufs with 4461 writes in 377583 ms (avoided 65646 new dirty bufs) for db 29:0

  • Anonymous
    August 18, 2013
    I've noticed this warning now on a VLD when performing ALTER TABLE statement (large table),  given I know that the database is on a SATA drive (others on SAS) I guess its too slow to keep up with the write demands.  

  • Anonymous
    April 05, 2015
    We are getting in SQL Server 2012 SP1 without enabling traceflag

  • Anonymous
    July 15, 2016
    You explained he Long Checkpoint and recover interval. How to recovery interval and can it be tuned? Whats the point of writing blogs that without giving details ...

  • Anonymous
    November 07, 2016
    Saw messages in SQL Log during a Delete transaction running only on weekend. This transaction supposed to complete in 35 min was taking 2 hours and failing. Observed that Integrity Maintenance job was running at the same.Per default recovery interval SQL Server will write dirty buffers what ever become available in last one minute. In this case many dirty buffers will be waiting to flush which SQL cannot flush all of them.I think this is not just with Storage issue,we may also look at the job schedules and adjust accordingly..