Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Saturday, 18 February 2017

How to set Secondary File NDF file growth and restrict file size growth in MDF file in SQL Server

New File growth effect Primary MDF and Secondary NDF Added in SQL Server

s p_h e l p db db info

initially it was only 6 MB



Added some sample records to table on test db growth table


after added records you can see growth only on M D F file only


Now we added secondary file 

even if you decide to u n select on enable auto growth option on properties of db



it will give effect on auto growth as none


now we check s p_help db


now we insert sample data

it will give error as we disabled auto growth it will not allow data to insert as show error


we disabled auto growth so it will not allow data to insert see the error


So we should need to enable Auto growth on M D F then we are going to set auto growth option on
N D F then it will work as see below



s p_h e l p db of M D F and L D F info as see below


now we are going to alter database to new location as see below


take database offline then move the M D F file to new location as see below


Now move the database log file to new location as see below


after bring online database shows as new location F drive as see below


Now we insert some sample data as see below


Now we new insert data take effect on M D F  only but cant insert data on LDF see below



solution:

We should need to limit DB Growth in M D F file as see below



now we see help db info





we should need to change it secondary on clustered index /index on table otherwise table should increase on primary only


after change we insert 10000 Thousand records to table as see below


you can see NDF now growing but MDF is not growing itself