1

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been terminated. Msg 596, Level 21, State 1, Line 4 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 4 A severe error occurred on the current command. The results, if any, should be discarded.

enter image description here

While moving some indexes to a different filegroup, and at the same time applying compression= PAGE, I get the error message above.

This is an example of one of my statements:

        CREATE NONCLUSTERED INDEX idx_applicationID_activityDate  
    ON [dbo].[tbl_applicant_activity] 
    (  [applicationID] ASC  , [activityDate] ASC  )  
    INCLUDE ( [activityTypeID] , [applicantID])  
    WITH (  PAD_INDEX = OFF, FILLFACTOR = 100,
 SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
    DROP_EXISTING = ON,DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]  

It is too late in the day today, but for tomorrow, out of working hours my plan is to try to run these statements in batches and find the culprit, if any.

The database is part of an AG.

QUESTION:

What does this message really mean, and how does it interfere in my index creation?

1

As Kin implied in the comments, this is a problem with communication between the primary and secondary nodes of your sync-mode availability group.

There are a number of different causes for this. It's possible that the availability group failed over in the middle of the operation (which would cause the transaction to be rolled back and then that error occurs).

More generally, the error message:

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed

Means that the transaction log record associated with log sequence number 2599205:10500:111 either

  • couldn't be sent to the secondary, or
  • wasn't acknowledged by the secondary

You should be able to find clues regarding what is causing this communication failure in

  • the cluster log (see here for how to get that),
  • the SQL Server error log (look for messages about availability group role changes), and
  • the AlwaysOn_health extended event session.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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