0

I have a SQL 2019 AG cluster and I add and remove databases to the AG successfully with automatic seeding, using the Wizard. We are now trying to develop Powershell automation for the process. So what I see is that the database addition process appears to complete successfully - everything is green in SSMS, AG is healthy on dashboard in both replicas. However, querying sys.dm_hadr_automatic_seeding, the seeding current_state is 'FAILED' and failure_state_desc is 'SQL Error'. The errorlog also records failed seeding

I don't understand this, can anyone help to explain this please? Why is everything green, yet a failed seeding error is reported. If i didn't check seeding status in the dmv, going by the dashboard, I will say all is successful, would I be wrong then?

What is the dmv looking at and how does it's report affect the operation of the system? Has my database addition process been successful, can I proceed and ignore the dmv report?

Thank you.

6
  • 1
    You should probably add the relevant errorlog and dmv output so we can further help. Jan 17 at 11:42
  • @SeanGallardy, thank you. Please see pasted below the query and result: select current_state, failure_state_desc, * from sys.dm_hadr_automatic_seeding order by start_time desc
    – PTL_SQL
    Jan 17 at 17:35
  • Current_state = FAILED; failure_state_desc = SQL Error
    – PTL_SQL
    Jan 17 at 17:39
  • bits from the errorlog: ….Always On: DebugTraceVarArgs AR 'Seeding is canceled with cancelReason = 2' ….Transitioning from [CATCHUP] to [FAILED].' ….Seeding task failed with result 0x00000002.' ….Always On: DebugTraceVarArgs AR 'Seeding is canceled with cancelReason = 20'
    – PTL_SQL
    Jan 17 at 17:41
  • What about sys.dm_hadr_database_replica_states from the secondary replica for that database? Does it show up? Jan 17 at 18:10

1 Answer 1

3

According to what you're stating is in the DMV output, the database is on the secondary replica and synchronizing. That means, if you didn't manually seed it, auto seeding worked to the degree of having the secondary database in a state which could connect to the primary and get data (more on this at the end). Either way, the secondary database is apparently joined and communicating with the primary, so it would seem everything is fine now.

To the point that the seeding failed, that DMV (sys.dm_hadr_automatic_seeding) holds the output of the seeding attempts. I would expect, if it seeded after the failed attempt, that there would be another entry showing that it completed. If there isn't one, then with the data given it's not possible to explain what had occurred.

The seeding failure was SQL Error which is a generic failure for some SQL based error had occurred. Where? Don't know. It occurred, seeding failed, the end. You'd have to go through the errorlogs and see if anything was bubbled up there, and if nothing in the errorlogs and the issues is reproduceable then to create a seeding XE session to look further into it.

What's also odd is that, apparently, the primary did wait for catchup:

Transitioning from [CATCHUP] to [FAILED]

So the secondary either had an issue with data movement starting, the primary had an issue with creating a log scan, or some other issue occurred which stopped the secondary from either starting or finishing the state transition. The reason this is odd is because this is essentially the last state and it means everything else ran fine. It also means there should be something in the errorlog stating what occurred, though we're at the mercy of what data has been shared.

2
  • Thank you so much for your time and the response. As far as I can see, I don't see anything in the errorlog, however, I will investigate further. If I find anything I will report back. Thanks again, I am very appreciative.
    – PTL_SQL
    Jan 18 at 16:48
  • just to update that, so far, what I can still see is that the secondary database is joined and is communicating with the primary. Even though the reported SQL Error message and failed seeding entries in the errorlog persist for bigger databases, everything else however seems to be ok so far. I can failover the replicas and query the big database on the primary after adding it to the AG. Once again, thank you very much for your time and willingness to help always. I am still keeping an eye on the system.
    – PTL_SQL
    Feb 11 at 0:22

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.