question

SQLStudent7 avatar image
SQLStudent7 asked

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
sql-server-2008filegroupfile
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

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 ,

0 Likes 0 ·
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]
0 Likes 0 ·
Try populating the table with enough data to require file growth on the NDFs.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.