A production DB has eat up all space of disk D. So we added new disk E and created a secondary (ndf) file for the DB. E currently has 100GB and D has 20GB free space left in them. After few days when verified free space of D drive has dropped down below 15GB. So was wondering why DB still growing in D as it has ndf file in E drive. When verified noticed mdf file of D drive was set to ‘Enable AutoGrowth’ and to ‘Unrestricted Growth’. So this option causing the DB to grow in D drive till entire disk space will be consumed and when it won’t find any more free space to grow then it will start using ndf file.
In order to avoid this we have to change the option of file growth from ‘Unrestricted Growth’ to ‘Restricted Growth’ by giving a value which is higher than the current mdf file size. Suppose current mdf file size is 10GB and you can set it to a restricted growth of 12GB(make sure we have ndf file which is set to Auto grow) and once mdf gets filled up(that is till 12GB) data will start storing in ndf file.
If its not set to restricted growth also this is not an issue as SQL Server will automatically decide to grow in ndf file once mdf gets filled up but till the time the entire space of disk will be eaten by mdf file if it’s set to grow unrestrictedly.