Sql-server – Query to Find Out How Many Days Until Database Is Full

sql servert-sql

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!

Best 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.