Enterprise Vault SQL Server Fragmentation

Dear All,

  I got the warnning about SQL tables have one or more indexes with a fragmentation level of more than 30%. What should I do? To rebuild index in SQL server? Is there any maintenace task I would need to do on SQL Server?

Thanks

Best regards,

Elroy

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

in some extreme cases you

in some extreme cases you might have to stop the EV services but it shouldnt be necessary on an ongoing basis. i dont know how you're going about rebuilding the index but if you're just using the builtin wizard, you might also want to look into other options or consult a DBA.

View solution in original post

10 Replies
Highlighted

generally speaking, this

generally speaking, this article covers standard SQL maintenance procedures.

How to automatically back up and perform recommended maintenance for Enterprise Vault SQL databases

you can leave out the shrink operation.

Highlighted

Dear Sir,   Can I have more

Dear Sir,

  Can I have more specific instruction for the SQL maintenance? Should I stop all the enterprise vault services and run a SQL maintenance job to rebuild index? Thanks

Best Regards,

Elroy

Highlighted

it's not necessary to stop

it's not necessary to stop the services but i suppose you could put EV in backup mode. if you dont do that, at least make sure your archiving window doesnt overlap with your maintenance job

Highlighted

Noted. I have tried to

Noted. I have tried to rebuild the index. However, the fragmentation is still high after rebuilding index. Would you please advise? Thanks a lot

Best Regards,

Elroy

Highlighted

how do you know that

how do you know that the fragmentation is still high?

Highlighted

Dear Sir,   It is because I

Dear Sir,

  It is because I still got the high fragmentation warning and also I have browsed to the SQL table and checked a few tables of the extend fragmentation is over 50%

Thanks

Best Regards,

elroy

Highlighted
Accepted Solution!

in some extreme cases you

in some extreme cases you might have to stop the EV services but it shouldnt be necessary on an ongoing basis. i dont know how you're going about rebuilding the index but if you're just using the builtin wizard, you might also want to look into other options or consult a DBA.

View solution in original post

Highlighted

I have stopped the EV service

I have stopped the EV service to run the index rebuild. I have createda maintenance plan in SQL to do reuild index task and update statistics. Would you please provide any clues for this warning? Thanks

Best Regards,

Elroy

Highlighted

i've seen DBAs use this

i've seen DBAs use this one https://ola.hallengren.com/

Highlighted

Re: Enterprise Vault SQL Server Fragmentation

Run below query on SQL for the fragmented databse

USE <Vault store group name>
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 10.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 10.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO