High Availability Error

[Former Member]

This is the first day after our SQL 5.5 to SQL 11 switch.

I rebooted the mirror in our High Availability setup. When I tried starting the dtaabase on that machine I got the error "Failed to redo a database operation" and a little further "- Error: Table 'sa_load_table_data_142' not found"

Someone must've ran a load table statement whiole I was rebooting. However, we modified all our 'Load Table's to include 'WITH CONTENT LOGGING'. Should that not have taken care of it? And even if we missed one, shouldn't the database have refused the command? Do I need to take extra steps to avoid this from happening in the future? The fact that I run into this on day one leaves me a little worried.

It seems to me that the only way to recover from this is to shut down the main server, copy the database from there onto the mirror, then start both, have them connect with each other and the arbiter, and I have a working mirrored server again. This is a problem for me, as I really can't shut down the database during the day (which is what would happen if I take the primary down, as I have no mirror right now).

How do I recover from this without having to shut down the database? (Is there a way?)

[Breck_Carter]

Do you have a diagnostic log file showing the error messages? If so, please post it. If not, you should run all your servers with the -o filespec.txt option, IMO, just in case the server writes something interesting.

[Former Member]

At this point I am affraid I do not have those logs (anymore, if I ever had them). I will follow your advise and make sure I run the servers with that option -o though.

[Breck_Carter · Accepted solution]

Try running dbbackup against the running primary server, and have it send the backup *.db and *.log files over to the other computer. Then start the database server on that computer as you would normally (as a participant in the HA setup). It should start up in recovery mode (because the backup was done while the primary was running), apply the transaction log file, and then become the secondary server after it talks to the arbiter and the primary.