0

Any idea why synchronization failed to asynchronous node. The database got corrupted and a new database had to be created and synchronization reinitialized.

Asynchronous synchronization failed with error:

During redoing of a logged operation in database 'MYDatabase', an error occurred at log record ID (4234:121234:6). Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.

Error2:

Time-out occurred while waiting for buffer latch type 3 for page (1:9), database ID xx.

Improve this question

1 Answer 1

Reset to default
1

Any idea why synchronization failed to asynchronous node.

Nothing more than what the errorlog shows.

During redoing of a logged operation in database 'MYDatabase', an error occurred at log record ID (4234:121234:6). Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup,or repair the database.

Along with

Time-out occurred while waiting for buffer latch type 3 for page (1:9), database ID xx.

There was some logged operation which either explicitly or by way of another mechanism wanted to update the boot page (1:9). This wanted a latch of type 3 which is an Update latch and it ended up timing out, which means it waited 300 seconds (5 minutes) which is the timeout value (not configurable).

There are various reasons this could occur, which comes down to two types of buckets.

The first are the ones that you can just resume and everything is fine, which means that this was due to some contention on the latches due to various internal and external reasons.

The second are the ones where resuming continually fails at the same LSN. These are hard failures and the database will need to be restored.

Regardless which bucket, there's essentially no chance to figure out why it happened after the issue occurred if there was no data collection setup and completed before the issue occurred. Thus, it's going to be impossible to say why just from this data.

Improve this answer
3
  • Thank you for your response. Issue occurred late at night so nothing was recorded when the issue happened. Could you clarify what data collection are you referring to so that it can be set up for future reference in case this issue reoccurs, or we can possibly trap it
    – mk SQL
    Apr 27 at 17:56
  • At a minimum you'd want a filtered dump when the issue occurs, a copy of a log backup with those LSNs, a copy of the database from when it failed to apply, output of locks, running transactions, XE of latch acquire/release, snapshots of workers, snapshot of schedulers, perfmon of sql counters + system counters such as memory, cpu, page file, etc.
    – Sean Gallardy
    Apr 27 at 18:44
  • It's a non-trivial collection and analysis.
    – Sean Gallardy
    Apr 27 at 18:45

Your Answer

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

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