FILLFACTOR = 20 caused Databases to Double in Size

Question

Post reply

FILLFACTOR = 20 caused Databases to Double in Size

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 16, 2014 at 8:34 am

#309154

I set the FILLFACTOR = 20 and it caused the Databases to Double in Size. One of the Database went up to
If I specify 100 then it will not allocate any free space right?
I generated code from the maintenance plan and I'm currently executing it with a FILLFACTOR of 100.
I need to shrink the Data files.
Is that the correct approach?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I'm rebuilding the Indexes.

I have rebuilt a lot of tables but I only have 9% free. It does not appear to be changing.

What am I doing wrong?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I rebuilt the Indexes on most of the tables in one of the Database and I only have 20% free.

I thought that I would have more than that.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

First thing to understand is FILLFACTOR. Here is what it says under CREATE INDEX:

FILLFACTOR = fillfactor

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity, but some space remains within the upper level of the index tree to allow for at least one additional index row.

With a fillfactor of 20, only 205 of each leaf level page contains data. This means 80% of each page will be empty initially. I can see why this would increase the size of your database.

The next question I have is what is it are you actually trying to achieve?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Initially I just wanted to reduce fragmentation.

Now I'm trying to reduce the size of the Data Files.

The ndf reduced pretty well but the mdf went from over 422 GB to almost a Terabyte.

I tried the following to shrink the file but I got an error.

DECLARE @DataFileName VARCHAR(255);

SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);

DECLARE @TargetSize INT;

-- Select current size and substract 500 MB

SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 1500;

EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');

Error:

Could not adjust the space allocation for file 'MainDW'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I shrinking the file from SSMS. I specified reorganize before releasing space.

That could take some time on a .9 Terabyte File.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Still slightly confused. Are you shrinking first then rebuilding indexes or the other way around?

And, why are you shrinking the database anyway?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Lynn Pettis (6/16/2014)
Still slightly confused. Are you shrinking first then rebuilding indexes or the other way around?
And, why are you shrinking the database anyway?

I'm rebuilding first because there was barely any free space to shrink so I'm rebuilding with a fillfactor of 100.

The database went from .442 GB to .9 TB. I need to shrink it. Dumb move.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I was thinking Oracle Free Space. :blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/