Saturday, September 14, 2013

Database growth if it is only primary file group with multiple data file disks

Database growth if it is only primary file group with multiple data file disks

SOme how the database created long back and as given below configuration/files/filegroup/space.

SQL Server 2005
mdf  --200GB ( on primary) - DISK1 --(Currently mdf file full size of data is 150GB)
ndf1 --100GB ( on primary) --DISK2 --(Currently mdf file full size of data is 20 GB)
ndf2 --100GB ( on Primary) --DISK3 --(Currently mdf file full size of data is 20 GB)
ldf  --100GB (not applicable) --DISK4((Currently mdf file full size of data is 50 GB)

NOte: ndf1 and ndf2 should be separate filegroup(Secondary) for better I/O. It was not happened.

Question: The database mdf,ndf1,ndf2 files creatd on same filesgroup "PRIMARY".It has only one filegroup. MDF fle size 150 GB now, If mdf file size full(200GB), then will it automatically go to ndf1 then ndf2.The DB growth is not restricted to anyone of them.

How the database will grow ?
mdf-->ndf1--->ndf2 data growth?
Will it be round robin ?  --I think it will not, because seconday filegroup does not exist.


I read below but did not clear to me.
--http://msdn.microsoft.com/en-us/library/ms189563(v=sql.90).aspx


It is worth to read these articles:

•http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068
•http://sqlskills.com/blogs/jonathan/post/Looking-at-multiple-data-files-and-proportional-fill-with-Extended-Events.aspx

No comments:

Post a Comment