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',
FragmentationLevel1 = 5, FragmentationLevel2 = 30, FillFactor=80, 
UpdateStatistics = 'ALL', OnlyModifiedStatistics = 'Y', LogToTable = 'Y'" -b 
  • Can you see any DBCC Shrink commands in the SQL ERRORLOG? – Kevin3NF Jul 3 '18 at 18:40
  • My bad .. I read it auto shrink turned on but not the dont part :-) I have deleted my answer. – Kin Shah Jul 3 '18 at 18:43
  • 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 – SQL_NoExpert Jul 3 '18 at 18:51
  • 1
    Were you previously using a fillfactor on your indexes of less than 80? – Padwan Jul 3 '18 at 19:32
  • 1
    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. – Kin Shah Jul 3 '18 at 20:06

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.

| improve this answer | |

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.

| improve this answer | |
  • 1
    All my big indexes were 99% fragmented... it released 50 % used space after rebuilt... just worried whether there was any data loss or not – SQL_NoExpert Jul 3 '18 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.