1

I am seeing below error messages when we have moved/migrated databases to new server/instances from SQL2K12 stanadlone to cluster

last target outstanding: 20800, avgWriteLatency 30
average throughput:  69.12 MB/sec, I/O saturation: 243550, context switches 398948
FlushCache: cleaned up 579723 bufs with 388331 writes in 65526 ms (avoided 125170 new dirty bufs) for db 19:0

I have no clue if this points any issue with stoage.

Now the concern is, before we did migration i had similar error messages on the this instance but they were like coming after half hour 2 hours or may 15mins. no fixed pattern.

But after migration above error message keeps coming every 2 mins from the day we migrated. That is what sounds alarming.

On the old server from where we migrated, yes we had similar messages but again there was no fixed patterm, sometimes 2 mins, an hour may be after 4 hours, but not continuous like on new server.

I got some info here https://blogs.msdn.microsoft.com/psssql/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log/

But still i am confused how to deal with this and what exactly these messages point dba's to look for?

Please suggest

0

1 Answer 1

2

Those messages indicate checkpoint activity is occurring. Checkpoints in SQL Server 2012 occur frequently, depending on several configuration variables, and are typically not something to be overly concerned about.

Check the recovery interval for your server:

SELECT c.name
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'recovery interval (min)';

From the Microsoft Docs:

The default recovery-interval value is 0, which allows the Database Engine to automatically configure the recovery interval. Typically, the default recovery interval results in automatic checkpoints occurring approximately once a minute for active databases and a recovery time of less than one minute. Higher values indicate the approximate maximum recovery time, in minutes. For example, setting the recovery interval to 3 indicates a maximum recovery time of approximately three minutes.

What is your recovery time set to? Are you experiencing poor performance?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.