This may be an unlikely case, but we are trying to figure out what happens if we have a cross database transaction similar to below and then issue a commit and the server fails over after commiting to one log yet before commiting to the second:
INSERT INTO TEST1.dbo.employee (lastname, firstname)
INSERT INTO TEST2.dbo.employeeaddress (empId, number, street, city, state, zip)
SELECT 1, '1122','N. Hostess Ave.', 'Chicago', 'IL', '60000'
Based on my knowledge, if the server went offline or failed over at this exact moment between "commit" being written to one log and then the other, upon startup only 1 database would show the inserted row. This is because 1 insert has been hardened to the log while the other would rollback on startup. Is this correct?
PFE - MicrosoftSQL Know-It-AllHow to post data/code on a forum to get the best help - Jeff Moden