Impact of asynch AlwaysOn synchronization on transaction log disk

• doodlingdba

  Hall of Fame

  Points: 3164

  More actions

  May 21, 2018 at 8:37 am

  #349560

  Hi there

  We have recently configured a 1 TB database in an asynch availability group. Once a month a massive ETL process loads data into this database. Early this morning the job failed due to: "Unspecified error occurred on SQL Server. Connection may have been terminated by the server." Our monitoring tool shows that the FusionIO disk where we store TempDB and our transaction logs is being hit hard during the job and we also see an error in the Windows Application log relating to FusionIO (something driver specific which we are following up with the vendor). 
  I'm not posting to ask for help with a resolution - it's more a case of trying to understand if AlwaysOn is adding to the load that lead to the failure. The developer who owns the process has made the comment that this issue never occurred before AlwaysOn was put in. The process was always intensive but have we taken it over the edge? As mentioned we currently host transaction log files and TempDB on the same disk - during the ETL process TempDB is hammered and the writes are all being recorded in the tran log. AlwaysOn will be reading from the tran logs in order to synchronize changes to DR - any thoughts on the impact of AlwaysOn on the tran log/underlying disk and whether we need to think about separating TempDB from the Tran logs? I know this is best practise but we due to server limitations we don't have any more disk slots so will need to re-jig things around.

  Any thoughts are appreciated!

  Thanks
  Doodles