I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb)
Is that normal behavior? I dont have auto shrink turned on – did I lose data?
Note: space used decreased in my database file; the actual physical size of my database file is unchanged.
Command used to calculate the space used:
SELECT sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB
Results of select @@version
Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) – 12.0.5579.0 (X64)
Rebuild of indexes performed using Ola Hallengren's script. Parameters used:
Databases = 'USER_DATABASES',
FragmentationMedium='INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE
',
FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
FragmentationLevel1 = 5, FragmentationLevel2 = 30, FillFactor=80,
UpdateStatistics = 'ALL', OnlyModifiedStatistics = 'Y', LogToTable = 'Y'" -b
Best Answer
You would not lose data with index maintenance / rebuilds. As your data is fragmented, the data would be spread over multiple leaf pages in the clustered / nonclustered indexes as data is deleted / updated over time leaving empty space in the page position where it was originally. Reorganizing / rebuilding the index recovers the space by reordering the data in the pages and releasing the space back to the database for future use.