Database Space Used Decreased after Index Rebuild (Ola Script)

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 

• Kevin3NF: Can you see any DBCC Shrink commands in the SQL ERRORLOG?
• Kin Shah: My bad .. I read it auto shrink turned on but not the dont part :-) I have deleted my answer.
• SQL_NoExpert: I restored a production database to a test server. There is no job that is running....When I compare the space i see the discrepancy
• Padwan: Were you previously using a fillfactor on your indexes of less than 80?
• Kin Shah: Can you check (ask around or dev team) if data was truncated before you did a rebuild ? thats the only bit left to check since you check most of the stuff.

Answer 1

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.

Answer 2

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.

• SQL_NoExpert: All my big indexes were 99% fragmented... it released 50 % used space after rebuilt... just worried whether there was any data loss or not