0

Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly complicated and takes long to run.

I am wondering if there is a simple solution using the following columns to calculate this. Columns I have are:

  • row_number
  • snapshot_time
  • instance_name
  • database_name
  • dbsize
  • current_usage

All tables are found in the MDW database. Any suggestions are greatly appreciated!

2
  • What is your current approach? e.g. Are you assuming linear growth? Jul 15, 2016 at 18:25
  • yes just the linear growth from snapshot sizes over the course of a year. Only issue is, i have a dataset with 65 million rows, so im looking a simple and effective solution :)
    – choloboy
    Jul 15, 2016 at 18:54

1 Answer 1

0

You will need to start building a history of the dbsize vs current_usage metrics by day (or more incremental if needed). Only then will you be able to make educated calculations for projected dates.

Otherwise you'll essentially just be setting a "hard-coded" linear curve that won't really be precise enough.

Basically, have a stored procedure that takes a snapshot of the current dbsize, current_usage and inserts it into a historical table. Once you have enough data, you'll be able to use that data to make your projections.

1
  • I think they'd need some hand holding on how you are meant to make projections. I think they know they have to, they're just not sure how to. Jul 16, 2016 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.