Does shrinking database reclaim index fill factor {%} from page also or fill factor % will be available still in page?

more ▼

asked Sep 07, 2011 at 11:52 PM in Default

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

DBCC SHRINKDATABASE will move datapages, not data within datapages, so the fillfactor should remain the same. What will happen though is that you are likely to get fragmentation from shrinking database files.

more ▼

answered Sep 08, 2011 at 12:26 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

As I know shrink database will increase index fragmentation because it moves data pages so we can DE-fragment indexes through reorganization of indexes, Is there will be more benefit If we do rebuild instead of reorganization after shrink or Re-organization is sufficient?

Sep 08, 2011 at 01:33 AM Sharma

If you have much fragmentation, you get a better result using rebuild than reorganize. But reorg and rebuild are likely to grow your database file again.

Sep 08, 2011 at 01:36 AM Magnus Ahlkvist

As I know Reorg only grow ldf file not mdf but rebuild grow mdf not ldf. (Simple Recovery Mode).

Sep 08, 2011 at 01:45 AM Sharma

You're right - reorg won't grow datafile. But on the other hand - it won't defragment very effectively either :)

Sep 08, 2011 at 01:52 AM Magnus Ahlkvist
  • I agree with Magnus. If you want to keep the growth to a minimum when using rebuild, you should specify the SORT_IN_TEMPDB option.

Sep 08, 2011 at 07:20 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 07, 2011 at 11:52 PM

Seen: 1590 times

Last Updated: Sep 07, 2011 at 11:52 PM

Copyright 2018 Redgate Software. Privacy Policy