locked
Resizing a LDF files and MDF files of SQL Server Databases RRS feed

  • Question

  • Hi friends

    I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:

    What would be the best practices (best methods) for provisioning a ldf file and mdf file?  Exists any generic formula that tell me an idea about this?

    Please, Can you give me a little guide for this?

    With this i want to avoid the shrink operation and the autogrow of sql server databases

    Greetings

    Aleds


    • Edited by Aleds Thursday, July 9, 2015 9:16 PM
    Thursday, July 9, 2015 9:15 PM

Answers

  • Hello Aleds,

    With the backup history you can query the amount of database growth of the past and with this you can calculate the required space for the next months.

    See:
    Database size growth as a pivot table
    Database size growth as a list

    Log Growth Rate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 10, 2015 6:22 AM
    Answerer
  • Hi Aleds

    Sizing depends on your workload. Initially set some max size and provision autogrowth by certain size. Here you need to understand that the size of autogrowth should be based upon workload.

    Frequesnt autogrowth will lead to fragmentation and impact performance.

    Friday, July 10, 2015 1:27 PM
  • Initially for any DB its set to max, once you go to properties and files you can browse the file and keep auto growth from 10 percent. and set the MAX limit if needed.

    Normally LDF will keep it max, to avoid shrink operation need to set recovery mode to simple.

    Again its purely depends on the business unit.


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

    Friday, July 10, 2015 1:43 PM

All replies

  • There are a fair amount of variables that can go into the equation.Is there expected growth or is the amount of data fairly static?

    I generally build my databases with 25-50% frees pace depending on the expected growth. I never want to get below 20% free space as this may affect the performance. 

    I definitely never want to fill up a log file so I tend to error on the side of caution and make it larger than I thing I need.


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, July 9, 2015 10:02 PM
  • Hello Aleds,

    With the backup history you can query the amount of database growth of the past and with this you can calculate the required space for the next months.

    See:
    Database size growth as a pivot table
    Database size growth as a list

    Log Growth Rate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 10, 2015 6:22 AM
    Answerer
  • Hi Aleds

    Sizing depends on your workload. Initially set some max size and provision autogrowth by certain size. Here you need to understand that the size of autogrowth should be based upon workload.

    Frequesnt autogrowth will lead to fragmentation and impact performance.

    Friday, July 10, 2015 1:27 PM
  • Initially for any DB its set to max, once you go to properties and files you can browse the file and keep auto growth from 10 percent. and set the MAX limit if needed.

    Normally LDF will keep it max, to avoid shrink operation need to set recovery mode to simple.

    Again its purely depends on the business unit.


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

    Friday, July 10, 2015 1:43 PM