locked
.mdf file is increasing.. RRS feed

  • Question

  • Hello All,

    We have a database in production which is increasing in size daily.

    I am planning to add  new file on other which is having enough space and make it as primary the growth of the db will be accommodated. Please let me know the procedure or the helpful link to perform this activity. Step by step process is appreciated.

    As it is production database I just want to confirm the below things

    1. Do we need any downtime for this?
    2. Did the users will get effected after the changes made to the db, ideally they shouldn’t get effected but just want to confirm once.

    Thanks in advance.

    Monday, January 19, 2015 10:32 AM

Answers

  • If the secondary data file is a part of the PRIMARY file group then it starts using that secondary file as well for data insertions. In such case unless both the files are full , there wont be any need for auto growth.

    Check this link http://technet.microsoft.com/en-us/library/ms179316(v=sql.105).aspx Heading "File Size"

    "SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs in a round-robin fashion."


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by mito access Wednesday, January 21, 2015 6:56 AM
    Tuesday, January 20, 2015 12:04 PM

All replies

  • Adding a new file is an online operation and I never had any issues doing it online.

    However, check if Instant file initialization is ON, which will reduce the time it takes to create the new file considerably.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Michelle Li Monday, January 19, 2015 2:41 PM
    Monday, January 19, 2015 10:53 AM
  • You can refer to this BOL article on how to add data file using SSMS.

    users wont be affected and no downtime is required.

    PS: Juts make sure when you are adding a file backup is not running


    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 Article

    MVP


    Monday, January 19, 2015 10:59 AM
    Answerer
  • What reason do you need a secondary file? Wher do you place it? Why not increasing a mdf file to allocate more space?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 19, 2015 2:40 PM
  • Hi Uri,

    Thank you for the response.

    Due to this .mdf file growth the disk space got filled.So,i planned to add new file to other drive which is having more space.


    • Edited by mito access Tuesday, January 20, 2015 9:12 AM
    Tuesday, January 20, 2015 8:53 AM
  • Hi shanky and aswin,

    Thank you for the reply.

    First, we will try to get an additional space on the drive from the san team based on the db growth because it is increasing daily.I feel that, some times it will be difficult to us to manage the db which is having more number of files.(Please correct me if i am wrong).

    In case we won't get a free space ,we are planning to implement any one procedure from the below list.

    Procedure 1:

    1.Add new file ie..ndf on the other drive which is having enough space.

    2.Restrict the growth on the .mdf file.So that,the db will start using this .ndf file instead of .mdf.

    Procedure 2:

    1.Create secondary file group and .ndf file.

    2.Add the .ndf file to secondary F.G

    3.Make the secondary file group as default one.Then the db will start using this new secondary file.

    Can you suggest me which is the correct and best one.




    Tuesday, January 20, 2015 9:11 AM
  • Both the procedure will work fine, but procedure 1 is enough I guess. As long as the second one is added, even if you didnt do anything else, eventually it will start using that ndf file.

    I dont think managing a db with multiple files is an issue. I have never come across such a issue. Are you reffering to any specific issue?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, January 20, 2015 9:24 AM
  • You can follow step 1 dont make environment complex by adding file group. Regarding data file increasing massively have you checked the autogrowth it should not be in percentage.

    Again do you take proper log backups (if your DB is in full recovery mode) to check log growth


    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 Article

    MVP

    Tuesday, January 20, 2015 9:43 AM
    Answerer
  • Nowadays it is a cheap to buy disk space.. 

    If you decided to add a secondary file, perhaps consider to move a largest table on to improve performance as well.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 20, 2015 10:06 AM
  • Thanks Ashwin.I have a small doubt on the below point that you mentioned.

    even if you didn't do anything else, eventually it will start using that ndf file.

    As per my understanding, in the case of restricting the file size only the data will go to the secondary file.

    Otherwise the .mdf file will grow in size till the disk space exhausts because there is no restriction on the file size.

     Please let me know if I am wrong otherwise kindly explain how eventually the data will go to the secondary file.

    Did you mean the data will be shared uniformly across all the files related to that particular db or the data will go to the secondary file once it is created.


    Tuesday, January 20, 2015 11:40 AM
  • Thanks Shanky.Auto growth has been kept in percentage.We are taking log backups and we don't have any issue with log file.
    Tuesday, January 20, 2015 11:40 AM
  • Thank you Uri.
    Tuesday, January 20, 2015 11:41 AM
  • If the secondary data file is a part of the PRIMARY file group then it starts using that secondary file as well for data insertions. In such case unless both the files are full , there wont be any need for auto growth.

    Check this link http://technet.microsoft.com/en-us/library/ms179316(v=sql.105).aspx Heading "File Size"

    "SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs in a round-robin fashion."


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by mito access Wednesday, January 21, 2015 6:56 AM
    Tuesday, January 20, 2015 12:04 PM
  • Thanks Shanky.Auto growth has been kept in percentage.We are taking log backups and we don't have any issue with log file.
    Why in percentage is auto growth in percentage for data file as well this may be what is causing SQL Server data file to grow out of proportion. Have you checked that ?

    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 Article

    MVP

    Tuesday, January 20, 2015 12:31 PM
    Answerer
  • Go to DB Properties, Go to Files perform the operation. This is online operation. No downtime required.


    Regards, Pradyothana DP. Please Mark This As Answer if it solved your issue. Please Mark This As Helpful if it helps to solve your issue. ========================================================== http://www.dbainhouse.blogspot.in/


    • Edited by PradyothanaDP Tuesday, January 20, 2015 12:41 PM Uploaded IMage
    Tuesday, January 20, 2015 12:40 PM
  • It is worth to check the larger tables in your database and then move some of the larger tables ( which are frequently updated) to another file.

    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

    Tuesday, January 20, 2015 12:46 PM