February 26, 2015 at 7:50 am
I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB.
My first question: Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.
Last question: If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?
Thanks you
February 26, 2015 at 9:44 am
Dan121 (2/26/2015)
I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB.My first question: Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.
Last question: If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?
Thanks you
The data is written proportional to the free internal space you have on each file. So for ndf1, if you have 100MB free, and for ndf2, if you have 500MB free, more extents are written or allocated to ndf2 until (in theory) both files becomes full at the same time.
If you have enough space and all are allocated on the same LUN or drive, I would not worry about that.
In my personal case, I only add secondary data files when I am running out of space or it's getting to big on a particular LUN and drive. Let's say I have mdf on SQL_Data. If I have SQL_Data2, I create a ndf in SQL_Data2 and I disable auto growth on the other.
How big the intial size? Well, It depends of your workload and database. If you have a 1TB database that gains MBs per day, you won't create a NDF that is 10MB, do you? you want something that won't expand itself; auto growths kill performance.
February 27, 2015 at 6:19 am
It also depends on how your filegroups are setup and how your objects are stored within the filegroup. It's entirely possible that you've got specific sets of data going to specific files, explaining the differences in size.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply