Fill Factor

Posted on 2003-04-01
Medium Priority
Last Modified: 2008-02-01
How do you remove fill factor from the entire database besides going to all of the indexes and removing manually.  Is there an automatic way.

Question by:ChrisCouture
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 13

Expert Comment

ID: 8247241

Accepted Solution

higginspi earned 100 total points
ID: 8247586
run "dbcc dbreindex" (see BOL for exact syntax)

It rebuilds the indexes for a given, and you can specify a given fill factor. In addition, you can change the default fill factor for future tables by (directly from BOL for 7.0):

"How to set a fixed fill factor (Enterprise Manager)
To set a fixed fill factor

Expand a server group.
Right-click a server; then click Properties.
Click the Database Settings tab.
Under Settings, select Fixed, and then position the fill factor slider. "
However, this will affect all databases on the server.

Of course, you have to type in the name of the table for each dbcc dbreindex command, but it will be less steps than clicking on every table and all the indexes.
To get a list of tables try:
SELECT  name
FROM [dbo].[sysobjects]
WHERE xtype = 'U'
(May be different in 2000, but works in 7.0)


Expert Comment

ID: 8247589
The selection of tables is from the database you are in, not for the whole server.

Expert Comment

ID: 8247621
And this process may take a *very* long time if you have millions of rows, and depending on your server maybe even a long time if you have tens or hundreds of thousands.  Also depends on how many indexes you have, etc.  

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question