question

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.

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 ,

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]

Try populating the table with enough data to require file growth on the NDFs.