locked
Require Some Suggestions RRS feed

  • Question

  • Hello Team

    Problem: We are running out of space on SQL Server data storage drive. Idea is to create a New Data File and Load data in that ndf file not any other file e.g mdf or other ndf files for DB

    Please provide some suggestion. I don't want to move any file from one drive to other with Detach/Attach or some other backup/Restore process.

    Data should be added to new File not any of the existing File.

    Tuesday, June 2, 2020 10:35 AM

Answers

  • What if i have another ndf file exist for DB.

    Now i will be having 1 mdf and 2 ndf(out of this 1 ndf already there)

    Not a problem a SQL Server can have multiple ndf files but only one mdf file. You can name files as ndf1 ndf2..etc

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, June 2, 2020 10:49 AM

All replies

  • That is possible add a new storage drive, add new ndf file and put it on new storage. Now go ahead and disable autogrowth on old mdf file. SQL Server will start writing to new ndf file and you would not have to move anything

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, June 2, 2020 10:39 AM
  • That add a new file and limit the max size of the existing files to there current size.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 2, 2020 10:41 AM
  • What if i have another ndf file exist for DB.

    Now i will be having 1 mdf and 2 ndf(out of this 1 ndf already there)

    Tuesday, June 2, 2020 10:48 AM
  • What if i have another ndf file exist for DB.

    Now i will be having 1 mdf and 2 ndf(out of this 1 ndf already there)

    Not a problem a SQL Server can have multiple ndf files but only one mdf file. You can name files as ndf1 ndf2..etc

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, June 2, 2020 10:49 AM
  • This Could be a good option but Just wanted to know any impact on DB if we Limit the Max Size of mdf file
    Tuesday, June 2, 2020 10:49 AM
  • This Seems to be a good Workaround. Tested it and results are good. Thanks .


    Tuesday, June 2, 2020 11:24 AM