x

DBCC SHRINKDATABASE V/S INDEX FILL FACTOR {%}

Hi,

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

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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 Amardeep
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 Amardeep
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
+1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x65
x3

asked: Sep 07, 2011 at 11:52 PM

Seen: 1324 times

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