Query

Question

Post reply

Query

mtz676

SSCrazy Eights

Points: 9212

December 13, 2023 at 2:04 pm

#4345228

ServerName DatabaseName DatabaseSize(MB) Date

Server DBName Size Date
A DB 110 6/1/2023
A DB 113 7/1/2023
A DB 118 8/1/2023
A DB 130 9/1/2023
A DB 120 10/1/2023
A DB 140 11/1/2023
A DB 143 12/1/2023
A DB2 1110 6/1/2023
A DB2 1113 7/1/2023
A DB2 1118 8/1/2023
A DB2 1130 9/1/2023
A DB2 1120 10/1/2023
A DB2 1140 11/1/2023
A DB2 1143 12/1/2023
A DB2 10110 6/1/2023
A DB2 10113 7/1/2023
A DB2 10118 8/1/2023
A DB2 10130 9/1/2023
A DB2 10120 10/1/2023
A DB2 10140 11/1/2023
A DB2 10143 12/1/2023

I would like to predict the growth rate for the next 12 months for each of these databases.

What is the best way(Query) this can be done based on the above data

December 15, 2023 at 8:52 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

How about providing your sample data in consumable format, along with your desired results? You've been here long enough to know better

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

and fix your data - these 2 look like they should be different db's

A DB2 1143 12/1/2023

A DB2 10110 6/1/2023

and make it clear if this are daily entries or monthly entries (for me they would be daily)

and this is the type of queries you should be able to figure out yourself - plenty of examples on google, including a few here on sqlservercentral.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Another thing to point out - your data looks to be inconsistent:

A DB 130 9/1/2023
A DB 120 10/1/2023

why did "DB" shrink by 10 MB that day/month?

But in the end, you are just doing simple math on this to determine the average growth per month based on the sample size... something along the lines of (max-min)/count would give you an approximate growth per month. Once you have that, the rest of the work is pretty easy to do. Now, what this won't tell you though is if you have outliers. For example, if every January you archive the previous years data from DB to DB2 then shrink DB, the size of DB will go down which will skew the math example I gave above. DB2 will also be skewed. What I usually do when doing something like this, since it is USUALLY a once per year thing, not an ongoing thing, is to export the data to Excel, put the raw data into worksheet "raw data", then make a new worksheet "calculations" where I put the current size of each DB on row 2 (row 1 is headers). Next, I make a 3rd worksheet that is "corrected data" which is initially a copy of raw data then do conditional formatting to highlight the min and max values so I can check for outliers. If they are outliers, delete the row in Excel and repeat until the data looks valid. Then it is simple math on "corrected data" and I can just extrapolate the data out in "calculations" as far and as frequently as I need. Generally, I do 1 month at a time for a 1 year period, then do yearly for 5 years. Another benefit of doing it in excel is then I have a point in time snapshot when I created the data and I can do a very easy "predicted vs actual" to see if I am on target to use the space I predicted or if I am going to need to request more disk and I can also compare it year over year.

Now, one fun thing about my company though is that processes and tools are changing constantly. For example, I was anticipating that we could retire one of our 400+ GB database systems roughly 2 years ago, but it is still in production but is now scheduled for retirement next year (2024). So planning disk space for a system that I thought would go away was a surprise when I was told to keep it online as the project to replace it got pushed out. Estimating sizes is a HUGE challenge in a constantly changing environment, so I just tell my IT department that I need 5% extra disk per year as a very rough estimate. SOMETIMES I need more, sometimes I need less, but 5% total gives me room to work with.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

mtz676 wrote:

ServerName DatabaseName DatabaseSize(MB) Date
Server DBName Size Date
A DB 110 6/1/2023
A DB 113 7/1/2023
A DB 118 8/1/2023
A DB 130 9/1/2023
A DB 120 10/1/2023
A DB 140 11/1/2023
A DB 143 12/1/2023
A DB2 1110 6/1/2023
A DB2 1113 7/1/2023
A DB2 1118 8/1/2023
A DB2 1130 9/1/2023
A DB2 1120 10/1/2023
A DB2 1140 11/1/2023
A DB2 1143 12/1/2023
A DB2 10110 6/1/2023
A DB2 10113 7/1/2023
A DB2 10118 8/1/2023
A DB2 10130 9/1/2023
A DB2 10120 10/1/2023
A DB2 10140 11/1/2023
A DB2 10143 12/1/2023
I would like to predict the growth rate for the next 12 months for each of these databases. What is the best way(Query) this can be done based on the above data

Please confirm or fix the data discrepancy that Frederico pointed out. And, since you've been around for a long time, you know what we'd like to see for data samples... "Readily Consumable" Data and you've been told this before.

After that, linear regression is pretty easy to do in T-SQL , with some "Readily Consumable" data. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Paste it into Excel and create a line chart then add a trend line.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Jonathan AC Roberts wrote:

Paste it into Excel and create a line chart then add a trend line.

Yeah... I was going to suggest that, as well. Then I remembered how many databases some people have per instance and it would be nice to have something that can auto-magically give you a heads up that you need to do such an analysis in Excel, especially if you have a database that lives on its own drive or own set of drives or you're planning a move to some new hardware or budgeting for additional storage or planning a move to the cloud, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Ok... the OP is a "No show" on the bit of extra information that I asked. Moving on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)