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:
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.
0 comments:
Post a Comment