I am running Minion Reindex 1.3 with DataCompression = 'PAGE' in the configuration.
On this index, I get the following error:
FATAL ERROR: Msg 153, Level 15, State 4, Server P-DB-SQLDBA-01, Line 1 Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.
This is the command being executed from IndexMaintLogDetails:
ALTER INDEX "PXML_Store_Demographics" ON "Sales"."Store" REBUILD WITH (FILLFACTOR = 90, PAD_INDEX = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
I do not see how I can override the DataCompression setting for a specific index. It appears that I can override that setting at a table level, but not at the index level.
How can I fix this so that the table maintains PAGE compression and the XML index does not cause this issue?
Additional information:
The database server is 2016 patched to the current level. The database compatibility mode is 100.
The current compression type on that table is Page. I can run rebuild commands on the clustered and the other nonclustered indexes with no problem. It only seems to have a problem with the primary XML index because it uses the DATA_COMPRESSION = PAGE modifier in the WITH clause.
Rick Sellers
I have a database that has the following index definition in AdventureWorks2008R2:
USE [AdventureWorks_2008R2]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [PXML_Store_Demographics] Script Date: 12/17/2018 11:21:54 AM ******/
CREATE PRIMARY XML INDEX [PXML_Store_Demographics] ON [Sales].[Store]
(
[Demographics]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
I am running Minion Reindex 1.3 with DataCompression = 'PAGE' in the configuration.
On this index, I get the following error:
FATAL ERROR: Msg 153, Level 15, State 4, Server P-DB-SQLDBA-01, Line 1 Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.
This is the command being executed from IndexMaintLogDetails:
ALTER INDEX "PXML_Store_Demographics" ON "Sales"."Store" REBUILD WITH (FILLFACTOR = 90, PAD_INDEX = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
I do not see how I can override the DataCompression setting for a specific index. It appears that I can override that setting at a table level, but not at the index level.
How can I fix this so that the table maintains PAGE compression and the XML index does not cause this issue?
Additional information:
The database server is 2016 patched to the current level. The database compatibility mode is 100.
The current compression type on that table is Page. I can run rebuild commands on the clustered and the other nonclustered indexes with no problem. It only seems to have a problem with the primary XML index because it uses the DATA_COMPRESSION = PAGE modifier in the WITH clause.