April 3, 2003 at 6:04 am
I have a database which is aprox 60 gig (data warehouse). What are the advantages of using multiple secondary files (ndf)? I can't seem to find much info on why to bust up the datafiles this way.
Thanks for any help
Ziller
April 3, 2003 at 6:13 am
You can use multiple filegroups with differents .ndf files and place them in another disk to win more performance for example. Or also can create a big db in one of those filegroups and backup a single big table.
April 3, 2003 at 6:14 am
I think, it only makes sense when you place the secondary files on different harddrives to improve performance by reducing reads/writes
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 3, 2003 at 6:16 am
The datafiles will be on a RAID 5 array, so I can't place them on different physical disks. Is there any issues with creating one large 60+ gig mdf file?
THanks
April 3, 2003 at 6:24 am
We have about 50 GB on a RAID 5. No problem so far
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 3, 2003 at 6:37 am
One (small) benefit of having several files is if you have to move the database to another server.
Recently, I had to move my database (100Gb) from one server to another one. Although the other server had approx 200 Gb free the largest partition was about 80Gb. As I had 3 files I could spread them out on different drives using a Move statement in a restore. I'm not sure if you can split a single file across drive if there is not enough space.
On another database, also about 100 Gb, I have some very large tables (25 million rows, 10 Gb each) and I put them on separate file groups. The reason being is that if I need to restore one table I can just restore that filegroup rather than the whole database (half an hour restore time vs 5 hours).
I have had a 200 GB file previously with no problems.
Jeremy
June 6, 2003 at 3:18 pm
I also have a large single file database stored on a RAID 5 array. The posters here seem to agree that creating filegroups that live on the array is not a good practice. My question relates to fragmentation. When I run diskkeeper on the partition that contains the large database, it defrags very poorly. I have 66% free space on the partition, so that is not a problem.
I have smaller databases on other servers that defrag well on RAID 5.
Is it possible that creating multiple filegroups will assist with this problem?
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply