Data_engine daily maintenance rebuild errors

Document ID : KB000034050
Last Modified Date : 19/02/2019
Show Technical Document Details
Issue:
I'm getting rebuild errors on data_engine log right after the daily maintenance. This error occurs on a few tables: Dec 20 00:52:24:641 [9080] de: ADO_Database::ExecuteSP - failed for spn_de_TableMaintenance  Dec 20 00:52:24:641 [9080] de: [Admin] ADO_Database::ExecuteSP com error for  - 1 errors Dec 20 00:52:24:641 [9080] de: (1) ADO_Database::ExecuteSP com error for  [Microsoft OLE DB Provider for SQL Server] Failed executing: alter index RN_QOS_DATA_0063_Idx1 on RN_QOS_DATA_0063 rebuild partition=396  with (online=on) : 'online' is not a recognized ALTER INDEX REBUILD PARTITION option. Dec 20 00:52:24:641 [9080] de: COM Error [0x80040e14] IDispatch error #3092 - [Microsoft OLE DB Provider for SQL Server] Failed executing: alter index RN_QOS_DATA_0063_Idx1 on RN_QOS_DATA_0063 rebuild partition=396  with (online=on) : 'online' is not a recognized ALTER INDEX REBUILD PARTITION option.
Resolution:
The error will appear in the data_engine log until you UN-check 'automatically reindex tables'. That should make it stop trying to index them.

Regardless, recommendation is to upgrade to NMS 6.2 to take advantage of the new database maintenance. It's likely that once you upgrade you can turn the data_engine reindexing back on.

'online' index rebuilds are not supported by SQL Server Standard.

online=on is only available in MS SQL Server Enterprise

Re Partitioning: Please upgrade to NMS 6.2 asap which contains a fix.

data_engine maintenance is completely re-done in NMS 6.2 especially regarding partitions and according to engineering is now "self-healing", so upgrading to NMS 6.2 is the preferred fix for partitioning issues.

The new maintenance routine will actually go through and find out if your partitioning schema is correct or not, and will fix it

If you are not not using SQL Enterprise you should NOT turn on auto-reindexing in the data_engine and you should instead have your DBA set up a nightly re-indexing job.

Please also find reindexing script attached that you can use for now prior to upgrading to NMS 6.2.

The query to run it in a job is:

use [msdb]
GO
exec [dbo].[DBA_RebuildIndexesBasedOnFragmentation] @maxfrag = 30.0, @databasename = 'NimsoftSLM', @rebuild = 1

(this will do table-locking rebuilds if the version is SQL Server Standard)

Note for the query to run successfully, change the database name if your database is not named 'NimsoftSLM.'

Attachments:

File Attachments:
TEC000003831.zip