Recommended maintenance plan for ePO 4.x database using SQL Server Management Studio
On the recommended maintenance plan, there is an available script to repair fragmentation of the db and rebuild indexes.
This is specially important if you are using intensive ressources from your SQL db (Risk Advisor and Policy Auditor).
It is recommended to run a reindex of the db prior to launching a MRA threat correlation task.
The proposed script attached to rebuild or reorganize the index based on the fragmentation is generating an error.
One of the command line is missing a '';'' (See the attached script document).
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @pagecnt bigint (Missing ‘’;’’?)
DECLARE @command nvarchar(4000);
DECLARE @pagelock int;
This script is a modifed version of the below Microsoft script to reorganize or rebuild based on the fragmentation level.
http://msdn.microsoft.com/en-us/library/ms188917.aspx (SQL Server 2008)
The error response we are getting is as follow:
Executed: ALTER INDEX [IX_PAOOvalDefinition_CVEVTDV] ON [dbo].[PAOOvalDefinition] REORGANIZE; UPDATE STATISTICS [dbo].[PAOOvalDefinition] [IX_PAOOvalDefinition_CVEVTDV]; [SQLSTATE 01000] Executed: ALTER INDEX [IX_PAOOvalDefinition_VCEVVTDV] ON [dbo].[PAOOvalDefinition] REORGANIZE; UPDATE STATISTICS [dbo].[PAOOvalDefinition] [IX_PAOOvalDefinition_VCEVVTDV]; [SQLSTATE 01000] Executed: ALTER INDEX [MTIS_ThreatThreatAssetHashPK] ON [MTIS_Threat].[ThreatAssetHash] REORGANIZE; UPDATE STATISTICS [MTIS_Threat].[ThreatAssetHash] [MTIS_ThreatThreatAssetHashPK]; [SQLSTATE 01000] Msg 1934, Sev 16, State 1, Line 1 : ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000]
Any help appreciated. Special tks to Greg and Caryn