changing the NDF files size

kamalbaaklini

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%

Kishorm

Go to File, Database, Alter and then specify the same size for each NDF file. See following MSDN link...

"http://msdn.microsoft.com/en-us/library/hh169188(v=nav.70).aspx"

bbrown

My first question is why so many NDF files? Are they on physically separate arrays? Not sure I see any real advantage if they are not. Even then, not sure there's a signifigant advantage with such small files. Considering the extra systems cost.

kamalbaaklini

bbrown wrote:

My first question is why so many NDF files? Are they on physically separate arrays? Not sure I see any real advantage if they are not. Even then, not sure there's a signifigant advantage with such small files. Considering the extra systems cost.

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 ???

kamalbaaklini

Kishorm wrote:

Go to File, Database, Alter and then specify the same size for each NDF file. See following MSDN link...

"http://msdn.microsoft.com/en-us/library/hh169188(v=nav.70).aspx"

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???

bbrown

So I guess I read your post wrong and they are not that small. :oops:


The T-SQL command "SHRINKFILE" should give you what you need. Used with its "EMPTYFILE" parameter. See SQL Books on Line for details.

David_Singleton

kamalbaaklini wrote:

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%

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.

bbrown

Another thing that cause this is adding additional data files to the existing filegroup and not rebalancing. See the comment about FBK above. If you are going to use multiple data files they really need to all be created up front.

kamalbaaklini

David Singleton wrote:

kamalbaaklini wrote:

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%

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

bbrown

I take it that all 7 files were not created at the same time?

kamalbaaklini

bbrown wrote:

I take it that all 7 files were not created at the same time?

yes true.

thats the main problem