Issue:-
The Very known issue with enterprise vault is slow archiving or will not able to process all the targeted mailbox. enterprise vault is highly depended on SQL server and keep sending a multiple request to SQL server database which leads the database fragmentation hence VERITAS highly recommended to configure the SQL maintenance plan with following option
1.Full Backup
2. Translation logs
(above can be taken care by backup solution)
3. Rebuild index
4. Update Statistics.
5. Clean up
To check the fragmentation level on database below is a query
Solution:-
SELECT
DB_NAME(DB_ID()) as DatabaseName,
OBJECT_NAME(ddips.[object_id], DB_ID()) AS TableName ,
i.[name] AS [Index_Name] ,
ddips.[Index_Type_Desc] ,
CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT)AS [Logical_Scan_Fragment_%] ,
CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT)AS [Avg_Frag_Size_in_Pages] ,
ddips.[Fragment_Count] ,
ddips.[page_count],
ddips.[avg_page_space_used_in_percent] AS Avg_Page_Dens,
ddips.[Partition_Number] ,
ddips.[Alloc_Unit_Type_Desc] ,
ddips.[Index_Depth] ,
ddips.[Index_Level]
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘Limited’) ddips
INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
AND ddips.[index_id] = i.[index_id] WHERE
ddips.[avg_fragmentation_in_percent] > 15
AND ddips.[page_count] > 1000 ORDER BY
ddips.[avg_fragmentation_in_percent] DESC,
OBJECT_NAME(ddips.[object_id], DB_ID()), i.[name]
Ref:-
Tech Experience By NISHANT 