Secondary Replica Suspended

Question

Post reply

Secondary Replica Suspended

alex.sqldba

SSChampion

Points: 10254
More actions
January 21, 2017 at 4:39 am

#318031

Morning Guys,
I have a secondary replica that has to date been working fine and then I think a SAN hiccup caused the log file on the secondary to become invisible to SQL Server (when the drive vanished momentarily, perhaps?) as a result AlwaysOn is suspended between 2 out of the 4 databases in that AG. The AG is still online and working for the other databases
The drive is visible now and I can browse to it.
Can I set the database at fault offline and online without causing any failovers do you think? I want to see if SQL will resume replica if I effectly re-present the logs that it thinks are missing
Cheers
Alex

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Have you tried just resuming the data movement instead?

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

Beatrix Kiddo - Saturday, January 21, 2017 6:49 AM
Have you tried just resuming the data movement instead?

Hi Beatrix,

Yes, indeed I have, which responds with the error "Failed to resume Data Movement (......) The log for XXX is not available"
Though I have confirmed I can see the log files and drive in question.

Cheers
Alex

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Oh, sorry- how annoying. What about removing the database from the AG then adding it back? Sorry if I'm stating the obvious.

alex.sqldba SSChampion Points: 10254 More actions · Answer 4

Beatrix Kiddo - Saturday, January 21, 2017 7:00 AM
Oh, sorry- how annoying. What about removing the database from the AG then adding it back? Sorry if I'm stating the obvious.

Hi, yes that's certainly an option.. But tat the moment it seems to be that the instance itself doesnt recognise that the log drive is available.

I dont particularly want to restart the sql services on there as it is in use for other stuff.. Hence I Was hoping that setting the DB's offline and online might do something... But not sure, and have no way to test at the moment elsewhere.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

Since it's not working anyway, why not try removing and re-adding it to the AG first, and if that doesn't work, taking it offline and bringing it back online. Worst case scenario you can re-sync it, right?

alex.sqldba SSChampion Points: 10254 More actions · Answer 6

Beatrix Kiddo - Saturday, January 21, 2017 7:12 AM
Since it's not working anyway, why not try removing and re-adding it to the AG first, and if that doesn't work, taking it offline and bringing it back online. Worst case scenario you can re-sync it, right?

Actuallly, when you put it like that - YES - It's no good it sitting there 3 days out of sync anyway! So what am I doing procrastinating!

Thanks Beatrix.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 7

And your transaction log might be growing really big as a result too, so don't leave it any longer than you have to.

alex.sqldba SSChampion Points: 10254 More actions · Answer 8

Beatrix Kiddo - Saturday, January 21, 2017 7:29 AM
And your transaction log might be growing really big as a result too, so don't leave it any longer than you have to.

I've got an eye on that, fortunately its not a particularly busy database (a lot of it is read only).

So, what are the effects of setting a DATABASE in a AG offline -- not the AG itself just the DB. Is it even possible?

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 9

I don't think you can while it's in the AG- just remove it, go on :-D.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 10

(I'm a bit bored having to work a Saturday for a datacentre migration-don't let me egg you on too much :).)

alex.sqldba SSChampion Points: 10254 More actions · Answer 11

Beatrix Kiddo - Saturday, January 21, 2017 7:54 AM
(I'm a bit bored having to work a Saturday for a datacentre migration-don't let me egg you on too much :).)

I've got 3 replicas, the primary one, this broken one and another one that used mostly for RO work.

If i remove the broken database(s) from this group, that wont impact the other replicas? As I dont want to have to rebuild the group over all 3 replicas... That would really put a hex on the movie I want to watch tongiht at the cinema!