x

Proportional Fill on ndf Files

Hi,

I am having issues when creating two ndf files (in addition to the mdf that sits in the primary filegroup). I have created these two seconday data files and assigned them to their own filegroup and set this filegroup as the default.

I have followed the rules of proportional fill (well, what I understand them to be!) to ensure that the physical size of the ndf's are the same and that they have the same amount of free space in the two files before I create an object in the user filegroup and populate it. However, on populating the table only one of the ndf files gets populated and grows whilst the second one remains untouched.

I was expecting the data to be split evenly between these two files and for their sizes to be identical?

I am running this on SQL Server 2008 STD x64 SP2

Any help or advice on this matter would be greatly appreciated

Thanks

more ▼

asked Feb 19, 2012 at 01:53 AM in Default

SQLStudent7 gravatar image

SQLStudent7
1 1 1 1

So you have 2 NDF files within a secondary filegroup? The way I read your post it sounds like you put each NDF in it's own filegroup. If that is the case then it will only write to that one file.
Feb 19, 2012 at 02:31 AM Shawn_Melton

Hi Shawn,

Thanks for your response. Apologies if unclear, the two ndf files are in the same filegroup;

USE [master] GO

CREATE DATABASE [SWTestFile2] ON PRIMARY ( NAME = N'SWTestFile', FILENAME = N'W:\MSSQL\SWTestFile2.mdf' , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ), FILEGROUP [SWTestFileFG] ( NAME = N'SWTestFile22', FILENAME = N'W:\MSSQL\SWTestFile22.ndf' , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ), ( NAME = N'SWTestFile23', FILENAME = N'W:\MSSQL\SWTestFile23.ndf' , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ) LOG ON ( NAME = N'SWTestFile2log', FILENAME = N'X:\MSSQL\SWTestFile2_log.ldf' , SIZE = 100MB , MAXSIZE = 2048GB , FILEGROWTH = 100MB ) GO

ALTER DATABASE [SWTestFile2] SET COMPATIBILITYLEVEL = 100 GO

ALTER DATABASE [SWTestFile_2] MODIFY FILEGROUP [SWTestFileFG] DEFAULT;

It is after this that I create a populate a table, that on running;

--now run after data population 
SELECT FileId, BytesOnDisk FROM ::fnvirtualfilestats(dbid(), NULL) WHERE FileId <> 2 --2 being logfile

shows the first ndf with all the data in it and the second untouched.

Thanks ,

Feb 19, 2012 at 08:52 AM SQLStudent7

I do not know how you make a decision from your query

SELECT FileId, BytesOnDisk FROM ::fn_virtualfilestats(db_id(), NULL) WHERE FileId <> 2 --2 being logfile

May be the number of bytes have not reached the 3 MB size, so BytesOnDisk field would have returning the same size for all the files.

Please run the following script, and find the details of filegroups, files etc. Please let us know the details after the execution

SELECT  fg.name AS [FileGroup]
,     df.NAME AS [FileName]
,       sc.name + '.' + ta.name AS TableName
,       i.name AS IndexName
,       i.[type_desc] AS IndexTypeDesc
,       8 * SUM(a.used_pages) AS 'Indexsize(KB)'
,       SUM(ps.[row_count]) AS NumberOfRows
FROM    sys.indexes AS i
        JOIN sys.partitions AS p
        ON p.OBJECT_ID = i.OBJECT_ID
           AND p.index_id = i.index_id
        JOIN sys.allocation_units AS a
        ON a.container_id = p.partition_id
        JOIN sys.tables ta
        ON [p].[object_id] = [ta].[object_id]
        JOIN sys.dm_db_partition_stats ps
        ON ta.object_id = ps.object_id
        JOIN sys.schemas sc
        ON ta.schema_id = sc.schema_id
        JOIN sys.filegroups AS fg
        ON fg.data_space_id = i.data_space_id
        JOIN sys.[database_files] AS DF ON [i].[data_space_id] = [DF].[data_space_id]
Feb 20, 2012 at 06:32 AM Usman Butt
Try populating the table with enough data to require file growth on the NDFs.
Feb 21, 2012 at 01:51 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
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:

x1850
x32
x18

asked: Feb 19, 2012 at 01:53 AM

Seen: 1304 times

Last Updated: Feb 21, 2012 at 01:51 PM