FlushCache Message In SQL Server

The FlushCache message came into notice for me when my scheduled backup jobs failed due to error - “FlushCache: cleaned up bufs”. FlushCache is the drill and pattern performed by SQL Server for carrying out the checkpoint operation. Additionally, the FlushCache is one such situation that is not experienced frequently by database administrators.

FlushCache Message SQL Server 3

Just imagine if we are trying to fetch a webpage but for some reason, we are unable to fetch the webpage for quite long and get the error as “404 – Page not found”. A similar situation can be compared here, wherein the SQL Server has a regular checkpoint logging. A checkpoint logging happens on the transaction log file that pushes to the hard disc. The basic intent of the checkpoint is to reduce the recovery time in the system failure event.

Further, the FlushCache message is recorded in the SQL Errorlog only while enabling the SQL Server Trace Flag – 3504. The Trace Flag – 3504 is responsible for recording and writing the checkpoint internal activity information in the SQL Server error log.

The Trace Flag – 3504 had to be enabled as a mandate operation prior to SQL Server 2012. The operation can be seen recorded in the error log by default in SQL Server versions after 2012. So, if you are not noticing them without enabling the Trace Flag then here is the guide for you.

You can check the Trace Flag status using the below command:

use <your database name>

dbcc tracestatus(-1)



You can enable the Trace Flag with the command:

dbcc TRACEON( 3504, -1)

Once the Trace Flag is enabled, your SQL system is now ready to record the FlushCache message in the SQL Server error log. Additionally, once the Trace Flag is enabled, you can confirm them by again using my first command – dbcc tracestatus(-1)  

FIX:

So normally saying, your system should be fine but sometimes could cause due to SQL Server overload. Further, most of the cases, the FlushCache erroring resolves by itself. But if you still keep noticing them, then please follow the below commands:

1. Check for all the disk drives and confirm that they are not filled over 85% (Per my assumption). Try to clear some space.
2. 
Run a complete check of the SQL statements running on your system and figure out the query that is spinning the I/O.
3. 
Run a complete throughput of your system performance.

According to the scenarios I have faced, the commands above should settle your system in peace. If you are facing any additional errors along with FlushCache that you are unable to resolve, please send a comment in the comment section and I can immediately respond with possible fixes.

0 comments: