June 23, 2010 at 8:01 pm
Hello DBA's,
Need to have your thoughts on
How to predict/forecast database growth ?
Environment: SQL server 2005, sp3 standard edition x64bit on win server 2003
Need some thoughts how different people achieve this ?
Below is what I have done,
1. analyzed the backup growth from msdb database.
2. collected stats on the mdf+ldf files over a period of time. (We have a crazy truncate log job, so will not rely on this for predicting.
Please dont ask me why we have this Tuncate log job, it was set up before I joined :hehe:)
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 23, 2010 at 9:20 pm
I do #1. Works well for my purposes.
June 24, 2010 at 7:23 am
Same here. We use #1.
Of course you might have fluctuations in that if you change your normal pattern of data processing, but using the backup size trend is pretty accurate for our purposes.
Of course using the backup size will give you an idea of the actual data whereas analyzing the mdf/ldf only would serve for purposes of free disk analysis, as you will, in most cases, have free space in the data/log files hence having a bigger size estimate.
June 24, 2010 at 9:15 am
I have a DBA database that I record database sizes in every week and keep for 365 days. I can then look at the statistics from that data and determine the trend of growth. Of course it's important to know if your user base and or general use of the system that access the databases will increase/decrease over the next year because that may impact the growth greatly!
Quest also has a tool called Capacity Manager that will record database sizes and statistics to help determine future growth.
June 25, 2010 at 12:13 am
Hawkeye_DBA (6/24/2010)
I have a DBA database that I record database sizes in every week and keep for 365 days. I can then look at the statistics from that data and determine the trend of growth. ...
However doesn't this method rely on 'autogrowth'?;-)
I call it 'AutoFragmentation' and it is especially undesirable for data files.
Maybe the backup size method is preferable because it works even when all files have been intentionally oversized to avoid autogrowth.
Cheers,
JohnA
MCM: SQL2008
June 28, 2010 at 9:06 am
In some cases yes but they have a max size. I use policy to alert if there's less than 2 growths left. On some servers I have plenty of disk space, and based on the number of users and history I let them autogrow without a max size. It all depends on the system I guess 🙂 Tracking your sizes is helpful for capacity planning, and if you have a new system coming online you will be able to identify a similiar sized environment that you've tracked to determine a possible starting size for your database(s).
June 28, 2010 at 10:38 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply