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

  • 1
    Can you post the details from sp_Blitz and sp_BlitzFirst as described here: brentozar.com/archive/2017/10/getting-help-slow-server That'll give us more details on what's happening with the server. A quick first guess is dramatically lower memory, for example. – Brent Ozar Mar 8 '18 at 20:25
  • @BrentOzar-> Hey Brent! I am huge fan of all the Brent ozar scripts and have been using them for last many years. But unfortunately we cannot deploy any 3rd part scripts in this env and if allowed this will take lot of approvals. is there any other way i can get some info for you , thanks still.. – BeginnerDBA Mar 8 '18 at 20:53
  • No, sorry, too much work. Best of luck with it though. – Brent Ozar Mar 9 '18 at 0:45
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?

  • Thanks Max for the input. On both old and new the value is set to 0 – BeginnerDBA Mar 8 '18 at 20:51
  • yes high disk disk readwrite latencies when viewing from monitoring tool , but no issues reported by users a such – BeginnerDBA Mar 8 '18 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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