Search code examples
sqlsql-serverdatabasedatabase-administration

Database Size Increase After Index Rebuild.How to get back down?

I executed an Index Rebuild for a large table in database because it was 85% fragmented. After the Index Rebuild finished the database file size has grown from 19 GB to 27 GB.

Rebuild option:

(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My Question : Is there any solution to decrease database file size without increase index fragmentation?

Solution

    1. You should have enough free space on database data file

    2. You should set auto-growth to a sensible size based database daily increase size

    3. The increase in database size due to the index rebuilt is expected.