Hi i have big DB with 7 NDF Files. as below
i want to have all NDF file to be same size. how can i do it?????? ](*,)
File Name Size (MB) File Growth
Data.mdf 267 10%
1_Data.ndf 221803 8%
2_Data.ndf 100000 20%
3_Data.ndf 100000 20%
4_Data.ndf 123346 15%
5_Data.ndf 117468 15%
6_Data.ndf 11066 40%
7_Data.ndf 20000 40%
these files on two separate physical arrays.
SMALL !! 220GB and 100GB and 100Gb and 117GB and 123GB and 11GB and 20GB
these files are considered small ???
not good solution for me to enlarge all small files to be 200gb. as you see i have large deference in sizes if i wan to enlarge all to be same size i will have a 2 terra database.
is their any way to backup the database and restore it in different file structure (less NDF files) and diffrent sizes???
The T-SQL command "SHRINKFILE" should give you what you need. Used with its "EMPTYFILE" parameter. See SQL Books on Line for details.
Its a very tedious and time consuming process. The best way is to make a Navision FBK backup, rebuild the database with the correct file sizes and start a restore, but on a database this size it is probably not possible.
I do see this a lot, and generally the cause is that someone was abusing google and sql books on line,a nd applying things that work in many SQL databases, but DO NOT work in Navision. Most commonly the creation of multiple file groups, which is very bad for Navision.
Assuming you have multiple file groups created, the first step is to break these and get all the data back into one filegroup. Once that is done you need to one by one create new files and move the data out of the existing firlegroup/file into the new one.
Step one is to work out how much free space is in each file, and what is the actual used space in the total SQL database.
I tried the idea of using FBK and recreate the database, i faced problem in the backup not successful.
the best way i found now, is to control the file growth manually to reach better file balancing.
Note: We have only one file group but 7 NDF files in it.
After upgrading the RAM from 64 to 128 we have better performance in long batch processing jobs (running time 55% ) and faster filtering in Forms.
Now im planning to add another array of Disks to split the NDF files on it
thats the main problem