Purpose of Multiple Data Files

[jfrost10]

Hey guys,

Just working through my MCSE SQL Server 2k Design/Implementation book, and have a few questions.

I'm just finished a chapter that talked about how you can have multiple datafiles for a database, and how you can set the growth rate, the max size, the default size etc., but it hasn't (yet) said anything abou the benefits that adding extra data files has.

Could someone explain a scenario where you would want the data files to have a limit (i.e. maybe some technique of taking data and throwing it in a data warehouse when the file gets a certain size?), and if there is any way to specifiy which tables/objects/etc. get put in which data files (if thats even possible)

Thanks for your time,

D'Arcy

 

[SQLSister]

Additional data files on other drives can improve performance especially in very large databases.

As to why you might want to limit growth, if more than one database is sharing a server, perhaps you would want to set a limit to ensure that no one database takes up all the space thus preventing the others from adding records. This would obviously require close monitoring on the part of the dba to ensure that the the files do not reach their limits without adjustment or the purchase of more drive space. It seems to be more usual for the databases to be set to unlimited growth.

 

[jfrost10]

Thanks Sister, the size thing totally makes sense now.


With the multiple data files though, the book made it sound that you could specify which tables you could put in certain files. Is this possible? I'm still a little shakey on how that all works.

Thanks again,

Jack