locked
Control growth of the Database file RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello folks,

     I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site.  Any recommendations on how to control the growth. 

    - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?

    Any suggestions ??

    Wednesday, October 7, 2015 2:02 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Do you know if the growth is from additional data in the tables or from fragmentation that hasn't been corrected bloating out the size of the files but having the files on a percentage used?

    As already mentioned, your growth size is too small .. however if you are growing by 10GB per month then you need to ensure that the SQL Server service account has been granted "Perform Volume Maintenance Tasks" in the server's security policy, otherwise you will be seeing long waits while all the pages are zeroed out.  With 1 MB growths, this might be getting slightly masked.

    Before setting the right size for the data file and the growths, you should decide on your archiving policy. If you can easily identify the data by date, then I would highly recommend partitioning as it can also be used to perform archiving deletions with little impact on your "live" part of the system. To do this you will need to create additional FILEGROUPS - perhaps 1 for each year of data. Each FILEGROUP requires and additional secondary data file to be created as well (hence the reason for doing this BEFORE extending your main file again). To move your tables to the new FILEGROUPS, simply rebuild the clustered indexes on the PARTITION SCHEME.

    With the data partitioned, you could then look at a sliding window form of Partition Switching to swap out your old data to a new table on a regular basis and then either delete or archive this without impacting the operational data.

    Once you have that in place it will then be time to think about the file sizes. My recommendation would be to work out how large the FILEGROUP needs to be for each year and set all the file sizes for the m to that size which will hopefully eliminate the need for the files to grow. You will need to have autogrow on the current partition as new data may still end up being more than the current size and you then use this as a safety valve.  Basically, auto grows do slow down the system so you should try to minimise them happening where possible

    Martin Cairney SQL Server MVP

    Friday, October 9, 2015 6:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Shanky,

    I do have the enterprise edition (2008 R2). Which option do you recommend partition or archiving. The end goal is to be able to reduce the disk utilization. We are unable to afford to add more GB to the drive.  

    Option1: I can take a full backup of the db. And then delete pre-2005 data. This is if we are never to use pre 2005 data

    Option2: Load all pre2005 data to a different db and move that file to a different storage favility and then delete pre2005 data from existing prod DB.

    Option3: Not sure how partition would save us disk space?

    If disk space is priority Archiving would be more useful but make sure you move archived records to different Machine , if you move it to different table in same machine they would still be using same disk resource. Partition is for making query faster and better management.

    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

    Friday, October 9, 2015 6:30 AM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello folks,

     1. I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site.  Any recommendations on how to control the growth. 

    2.  Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?

    Any suggestions ??

    1. This is bad , 1 MB autogrowth is definitely very bad. You should consider chaning it to 500-700 MB instead. You cannot as such control growth it is happening because data is coming.

    If you really want to shrink it you can use data and page compression but that would require a round of testing before implementation.

    2. What you are suggesting is like Archiving, you can do that. you can move old data to some other database and can reduce amount of data in current database.

    If you have enterprise edition you can use partitioning as well

    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

    Wednesday, October 7, 2015 2:14 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Shanky,

    I do have the enterprise edition (2008 R2). Which option do you recommend partition or archiving. The end goal is to be able to reduce the disk utilization. We are unable to afford to add more GB to the drive.  

    Option1: I can take a full backup of the db. And then delete pre-2005 data. This is if we are never to use pre 2005 data

    Option2: Load all pre2005 data to a different db and move that file to a different storage favility and then delete pre2005 data from existing prod DB.

    Option3: Not sure how partition would save us disk space?

    Thursday, October 8, 2015 6:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    doineedanid,

    Partitioning only makes sense if you have other drives.  You can make the business case for more drives or backup and move the data off to another machine or medium (Excel, Access, etc...).

    (Edit)Also, you should set the growth size to 500 - 750 MB.  This will help the server.

    Good luck!

    Don Gerard


    • Edited by Don Gerard Thursday, October 8, 2015 7:00 PM add text
    Thursday, October 8, 2015 6:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Shanky,

    I do have the enterprise edition (2008 R2). Which option do you recommend partition or archiving. The end goal is to be able to reduce the disk utilization. We are unable to afford to add more GB to the drive.  

    Option1: I can take a full backup of the db. And then delete pre-2005 data. This is if we are never to use pre 2005 data

    Option2: Load all pre2005 data to a different db and move that file to a different storage favility and then delete pre2005 data from existing prod DB.

    Option3: Not sure how partition would save us disk space?

    If disk space is priority Archiving would be more useful but make sure you move archived records to different Machine , if you move it to different table in same machine they would still be using same disk resource. Partition is for making query faster and better management.

    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

    Friday, October 9, 2015 6:30 AM
    Answerer
  • Question
    Sign in to vote
    1
    Sign in to vote

    Do you know if the growth is from additional data in the tables or from fragmentation that hasn't been corrected bloating out the size of the files but having the files on a percentage used?

    As already mentioned, your growth size is too small .. however if you are growing by 10GB per month then you need to ensure that the SQL Server service account has been granted "Perform Volume Maintenance Tasks" in the server's security policy, otherwise you will be seeing long waits while all the pages are zeroed out.  With 1 MB growths, this might be getting slightly masked.

    Before setting the right size for the data file and the growths, you should decide on your archiving policy. If you can easily identify the data by date, then I would highly recommend partitioning as it can also be used to perform archiving deletions with little impact on your "live" part of the system. To do this you will need to create additional FILEGROUPS - perhaps 1 for each year of data. Each FILEGROUP requires and additional secondary data file to be created as well (hence the reason for doing this BEFORE extending your main file again). To move your tables to the new FILEGROUPS, simply rebuild the clustered indexes on the PARTITION SCHEME.

    With the data partitioned, you could then look at a sliding window form of Partition Switching to swap out your old data to a new table on a regular basis and then either delete or archive this without impacting the operational data.

    Once you have that in place it will then be time to think about the file sizes. My recommendation would be to work out how large the FILEGROUP needs to be for each year and set all the file sizes for the m to that size which will hopefully eliminate the need for the files to grow. You will need to have autogrow on the current partition as new data may still end up being more than the current size and you then use this as a safety valve.  Basically, auto grows do slow down the system so you should try to minimise them happening where possible

    Martin Cairney SQL Server MVP

    Friday, October 9, 2015 6:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Martin,

     Your point of fragmentation got me thinking into seeing if that was a possibility. I ran some test and below are the findings. I did a select * from all tables with a filter of retrieving data from only 15 days. I also included the client statistics which would give me the size of the rows. Once I added all the 'bytes received from server" , it came to around 6 gb for 15 days. So basic math tells me its going to around 10-11 gb per month. Does this mean that the size growth is purely data and nothing else. 

    Also, it seems that archiving is the best option as we are fine with deleting/transferring old data to a different machine. My question is, once i delete the data are there any follow up commands that need to be executed to get system back to normal such as log space etc...  ?


    • Edited by doineedanid Wednesday, October 21, 2015 3:02 PM storage calculation
    Wednesday, October 21, 2015 2:53 PM