Return
-
Title
The Spotlight Statistics Repository database grows up to 50GB every night. -
Description
The Spotlight Statistics Repository database grows by 50 GB every night, filling the drive. After the drive fills up there is now 50 GB in the Spotlight Statistics Repository that is unused. Why is it filling up each night and why is there so much free space? -
Cause
The maintenance job recreates index from scratch with entirely new structure so it requires enough space to duplicate each existing non-deleted row, page-by-page - this is SQL Server behaviour, we have not changed the maintenance job functionality for years. Your playback database has a retention period 90 days which is 3 times more than the original period.To keep the database from growing, we can use index reorganization instead of index rebuild in the maintenance job. Index reorganization is a lightweight process that will solve the fragmentation in a less effective way but it does similar work like index rebuild. -
Resolution
WORKAROUND:
Run the attached SSRMaintenance.sql file on Spotlight Statistic Repository database to use reorganization instead of rebuild. If you use your own solution for the index reorganization, you can comment on the concrete commands in the procedure.
SOLUTION
Fix for the issue will be provided in a future version of Spotlight on SQL Server Enterprise.
-
Defect ID
DS-1236 -
Attachments