SQL Server : AlwaysOn Availability Groups data movement for database 'PROD' has been suspended

• santosh.mane

  SSChasing Mays

  Points: 611

  More actions

  December 20, 2018 at 6:56 am

  #369734

  Hi,

  We are using SQL Server 2012 having Always on High Availability setup ( Active - Passive nodes) with 2 TB database size.
  CPU : 8 virtual CPU
  Memory : 128 GB - OS and 80 GB to SQL server
  What vaule should be set to Parallelism for below paramter:
  Cost Threshold For Parallelism:
  Locks:
  Max Degree of Parallelism :
  Query Wait:

  Background :  We are having SQL Server DB with AlwaysOn Availability , data is uploaded from CSV files received into database on daily bases
                         as certain state the database went out of sync which lead to increase in Log file size 

  Received below messages :
  Event Log Error : “Time-out occurred while waiting for buffer latch type 4 for page (27:2250684), database ID 6.”
  SQL server Eror Message :
  AlwaysOn Availability Groups data movement for database 'PROD' has been suspended
  for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO').
  To resume data movement on the database, you will need to resume the database manually.
  For information about how to resume an availability database, see SQL Server Books Online.“Time-out occurred while waiting
  for buffer latch type 4 for page (27:2250684), database ID 6.”

  Please let us know
      1. Issue was fixed by resuming the data movement but why we received this error.
     2. How to find the root cause of this issue
     3. How to set alert when AlwaysOn Availability Groups data movement is stopped
     4. What health checks to be performent daily
     5. Did Parallelism vaules set at instnace level has created this issue
     6. how to check TEMPDB contenstion
     7. How to shrink the DB LOG file ( DB is i encrypted mode) , DB recovery mode is full

  Can you please advise urgently?

  Thanks & Regards,
  Santosh