Sql-server – Remote harden of transaction ‘CREATE INDEX’ – when moving an index to a different filegroup

indexindex-maintenancesql serversql-server-2016transaction-log

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.

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?

Best Answer

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.

Related Solutions

Sql-server – Found errors in table after executing DBCC CHECKTABLE command

Realizing this situation is long over I'm still going to put in my 2 cents for anyone else finding themselves in a similar situation.

You have a fair amount of corruption in that table. Run a DBCC CHECKDB on the database ASAP.
I hope you have a good & recent backup. That's going to be your best bet for recovery.
If you don't have a recent backup then you have a number of lesser possibilities.
- Use the dreaded repair_allow_data_loss. This is your LAST option for a reason. It can and probably will cause you to lose some data. And by some it could be one column of one row all the way up to .. well .. all of it. And you won't have any idea how much was lost.
- Create a new database with the same structures and use something like SSIS to copy the data across. Still probably going to lose data here.

Regardless, if you have the time I would look over the answers given in Steve Stedman's Database Corruption Challenge. He posted corrupt databases and various people posted their solutions to fixing them. These solutions range from the normal (as above) to some interesting and unusual solutions. One or more of them may work for you and let you salvage your database without data loss.

Also, and this is important too, if you have a corrupted database it's probably corrupt for a reason. Check your IO substructure etc to make sure there isn't a problem there. And also check the other databases on that instance with CHECKDB to make sure you don't have additional problems.

Last but not least. You should be running regular backups, testing them, and running regular CHECKDBs on all databases for situations exactly like this. The sooner you understand you have a problem the better chance of fixing it without loss. Not to mention having good, recoverable backups just in case.

Sql-server – Parent-Child Tree Hierarchical ORDER

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Best Answer

Related Solutions

Sql-server – Found errors in table after executing DBCC CHECKTABLE command

Sql-server – Parent-Child Tree Hierarchical ORDER

Related Question