Data files in filegroup

Question

Data files in filegroup

jarretg

SSCommitted

Points: 1706
More actions
October 2, 2003 at 9:34 am

#64552

Hello everyone,
I have a database spread out over 5 files in a secondary filegroup. Each of the files is on a seperate logical drive. When I check the properties of the database, the space allocated for each data file looks like this:
First data file - 2.4GB
Second data file - 650MB
Third data file - 1.5GB
Fourth data file - 1.3GB
Fifth data file - 1.5GB
All data files are set to grow automatically at 25 percent with no restriction on maximum file size. My problem is that if this database grows a few more times, the first file will outgrow the free space on its respective drive.
Does anyone know a reason why the first file would be so much larger than the rest?
A couple of fixes I've came up with are:
1. Expand the first drive to hold more space.
2. Place a max file size on the first file and let the other data files play catch up.
Any other ideas?
All comments and/or suggestions are greatly appreciated.
Thanks in advance for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 1

If I remember correctly, a table is only on one file. Perhaps there is a table(s) that is growing larger on the first filegroup and so this file grows disproportionately?

Are there large tables in one file? You might be able to move them (by moving the clustered index) to another filegroup.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

mccork SSC-Insane Points: 22288 More actions · Answer 2

Steve,

Actually a table spreads itself across the filegroup to which it's assigned.

Jarretg,

I've seen this happen before, but not as radically as your example. It's mainly due to the order in which SQL expands filegroups when they all become full. Only one is expanded at a time. However, this doesn't explain why datafile #2 has been left behind.

We've managed to get things more under control by stopping and setting autogrow on specific files so that SQL would choose the one we wanted it to choose.

Once back at roughly the same size, we've set autogrow OFF for all files and thereon monitored and managed it a bit more strictly than other databases, keeping an eye on total filegroup usage and scheduling an expansion out of hours.

It sounds like you're using filegroups for performance, so in that case you really don't want autogrow anyway. Scheduled expansions are the way to go. (imho )

Cheers,

- Mark

Cheers,
- Mark

jarretg SSCommitted Points: 1706 More actions · Answer 3

I'll turn off autogrow on all but data file #2 and let it catch up, then set it to "Off" for autogrow and just schedule expansions.

Mark - How do you determine the total filegroup usage?

Thanks for the suggestions!!!

mccork SSC-Insane Points: 22288 More actions · Answer 4

I usually look for filegroups creeping over an arbitary threshold, in this case 90% full, with something along the lines of:



DECLARE @PctFullThreshold SMALLINT 

SET @PctFullThreshold = 90



DECLARE @groupid INT, @LimitMB DEC(11,2), @UsedMB DEC(11,2), @PctFull DEC(7,2), @Msg VARCHAR(8000)



WHILE 1 = 1 BEGIN

    SELECT TOP 1

        @groupid = fg.groupid,          -- Group ID from sysfilegroups

        @LimitMB = SUM (                -- Combined file sizes from sysfiles for the group

            CASE                       

                WHEN f.growth = 0 THEN f.size               -- No Growth, so current 'size' is it

                ELSE 

                    CASE 

                        WHEN f.size > f.maxsize THEN size   -- choose bigger of current 'size'...

                        ELSE f.maxsize                      -- or 'maxsize'

                    END

            END / 128.0 ),                                  -- stored as 8K pages, so convert to MB

        @UsedMB = (SELECT SUM(reserved) / 128.0 FROM sysindexes i WHERE i.groupid = fg.groupid AND i.indid in (0, 1, 255)) 

        FROM sysfilegroups fg

        JOIN sysfiles f ON f.groupid = fg.groupid

        WHERE NOT EXISTS

           (

            SELECT * FROM sysfiles f              -- There must be NO files...

            WHERE f.groupid = fg.groupid          --  within that group...

            AND f.maxsize = -1 AND f.growth > 0   -- that are unrestricted in growth

           )

        AND (fg.groupid > @groupid OR @groupid IS NULL)

        GROUP BY fg.groupid

        ORDER BY fg.groupid

    IF @@ROWCOUNT < 1 BREAK

    SELECT @PctFull = @UsedMB / @LimitMB * 100.0

    SELECT @Msg = 

                'Database ' + DB_NAME() + 

                ', Filegroup ' + RTRIM(groupname) + 

                ', Total=' + CONVERT(VARCHAR, @LimitMB) + 'mb' +

                ', Used=' + CONVERT(VARCHAR, @UsedMB) + 'mb' +

                ' (' + CONVERT(VARCHAR, @PctFull) + '%)'

        FROM sysfilegroups WHERE groupid = @groupid



    IF  @PctFullThreshold >= @PctFull

        PRINT @Msg

    ELSE

        RAISERROR('%s', 11, 1, @Msg) WITH LOG

END

Cheers,

- Mark

Cheers,
- Mark

sxg6023 SSCrazy Points: 2918 More actions · Answer 5

I have seen this behavior, while I was creating few non clustered indexes on one of our tables. I had three files in the file group, both set to auto grow and on the same drive. When all set and done, one file was 30GB, one was 12 GB and the last one was 4 GB. Since I wanted proportinal fill, I dropped the indexes, dropped the files and manually added files to the group with preset sizes of 16 GB each and no auto growth. Now the files are being used proportionately. So, I am leaning to think that the proportionate fill works better if the file group has auto growth turned off. I That would reduce the fragmentation as well.