Question :
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!
Answer :
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.