We have space issue in Drive 2 which host the mdf file for a database of 260GB, currently free space is less than 20GB and data grows 3Gb per week.. We can not increase disk space for next 2 months due to business compulsion.. But we have 80GB of free space in Drive 3 so my plan is to add one more data file .ndf and auto increment it to 3 folds of current mdf file's auto increment size(as it uses round robin algo to add data the new ndf will hold most of the incomming data)...

I test this is in my local machine it worked fine, Just want to get some advise if this is a good approach? what are the disadvantages in this? Is there any other option to get some space like using filter index or any administration approach..

we do not have space to get from shrink  too... please let me know your input

Hi,

You may refer following link with similar query.

dd new file group to existing database

Yes , it looks ok, but in the next 6 months your Drive 3 will be full and what do plan to do so?

Do you have one table growths dramatically ? Is that possible to move an 'old' data to the archive ( possible another db/instance)?

You can add secondary data file on D drive and then can disable autogrowth for primary data file in that case data would only be written to newly added secondary data file

Thanks Praveen, Will check on the link

Hi Uri, Yes possibly in next another 3 month we will add space am looking for a quick solution as we have some more space in drive 3 :)

We are also doing archival process, but still it does not  help much as due to locking we keep it low dead lock priority... moreover our transactions is getting high day

Thanks Shanky disabling auto growth is the part i did not cover up..

Hello ,

You should use ndf file unchecked the auto goth of primary file . If the system is running through a single controller, then splitting is unlikely to have a major impact. But, generally, I would put data on one file (that means clustered indexes), indexes on a second and LOB data on a third. If those go on different disks, good. I would only add additional files if I saw a specific need for them.Example:

USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE (NAME = N'AdventureWorks2012_Data2',
FILENAME = N'C:\AdventureWorks2012_Data2.ndf',
SIZE = 10240KB , FILEGROWTH = 1024KB )
TO FILEGROUP [SecondFileGroup]
GO

You can also see the below link :
http://aboutsqlserver.com/2013/09/24/sql-server-storage-engine-database-files-and-filegroups/