cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Recommended maintenance plan for ePO 4.6 database - Fragmentation-Rebuild-Reorganize

Recommended maintenance plan for ePO 4.x database using SQL Server Management Studio

https://kc.mcafee.com/corporate/index?page=content&id=KB67184

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

1 Reply
McAfee Employee spamidi
McAfee Employee
Report Inappropriate Content
Message 2 of 2

Re: Recommended maintenance plan for ePO 4.6 database - Fragmentation-Rebuild-Reorganize

Please take a look at this posting - 

http://social.technet.microsoft.com/Forums/nl/winserverwsus/thread/13181437-8902-4bc9-9933-3a53183e9...

and this books online article:

http://msdn.microsoft.com/en-us/library/ms174393%28v=sql.105%29.aspx

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

Try changing the Index maintenance script to add a SET QUOTED_IDENTIFIER ON statement at the beginning and see if that helps.

More McAfee Tools to Help You
  • Subscription Service Notification (SNS)
  • How-to: Endpoint Removal Tool
  • Support: Endpoint Security
  • eSupport: Policy Orchestrator