Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Fill Factor

Posted on 2003-04-01
4
Medium Priority
?
373 Views
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.

0
Comment
Question by:ChrisCouture
  • 3
4 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8247241
Yes.
0
 
LVL 4

Accepted Solution

by:
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'
ORDER BY name
(May be different in 2000, but works in 7.0)

0
 
LVL 4

Expert Comment

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

Expert Comment

by:higginspi
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.  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

575 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