July 18, 2005 at 1:22 pm
In a nutshell, what is the advantage to multiple data files in a database. For example, one of the databases I inherited has 5 seperate data files with sizes ranging from 5gb to 34gb. They are all part of the same file group and all sit on the same physical and logical drive. I have been told that this was the recomended way of doing it. However I have never heard anyone suggest this type of setup. Before I suggest changing it I want to get some ammo from the knowlegable group here.
July 18, 2005 at 3:45 pm
From books online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Generally it is for improved performance, by putting files on different drives spreading Disk IO across may drives.
July 19, 2005 at 2:58 am
If this setup is not for IO, it could be for backup purposes. By backing up single files (rather than the whole DB), you can cut your backup times down to fit them into a given window.
Equally, the reason could be that certain files change regularly (and need a full backup every night), and others change very rarely (so a transaction log backup will do). Again, it's about keeping the length of time your backups take down to a minimum.
July 19, 2005 at 5:26 am
Everything everyone else said, plus, you can split off storage of text columns into a seperate file to keep maintenance of the text information seperate from the rest of your data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply