Question :
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
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.
This is quite common behavior. You are reclaiming unused space by reordering pages in the leaf level.
Just don’t shrink your datafile unless it’s critical – you’ll probably need that space in the future. You are just wasting time, fragmenting (again) the database and locking tables.