CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-administration
Hamed Naeemaei
Hamed Naeemaei

Reputation: 9658

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?

Upvotes: 0

Views: 1250

Answers (2)

Farzaneh Muhamad beigi
Farzaneh Muhamad beigi

Reputation: 26

  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.

Upvotes: 1

dbamex
dbamex

Reputation: 211

You can always try to shrink the datafile, but as you mention you could get fragmentation on that. I'd say, keep your datafiles the size they are, they are not so big and you will have some space to grow your DB.

Upvotes: 0

Related Questions