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?
User Biju jose has challenged the frame of your question in a comment, and I think it's probably the most helpful answer that you could get:


  I believe you should not shrink the database to recover the space since once you do a rebuild after that will cause it to grow again. The point here is your database should also have space to accommodate your maintenance task also!


If you really, desperately need these 8 GBs of space back, you could use DBCC SHRINKFILE on the data file to shrink it down some.  Note, however, that this will likely cause some fragmentation.  This unhelpful cycle (fragmentation -> rebuild -> empty space -> shrink -> fragmentation -> repeat) is described in detail here:

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

If you want a nice collection of additional reasons not to shrink files, check this out (and all of the articles it links to): Stop Shrinking Your Database Files. Seriously. Now.

Randolph West helpfully points out in the comments that if you do need to reduce fragmentation without growing the file so much, you could do a REORGANIZE of the index.  This requires less working space than the index, but also can only eliminate certain types of fragmentation (it rearranges and compacts the leaf pages of an index, but doesn't drop and recreate the whole thing like a REBUILD does).