SQL Server data drive (.mdf files) running out of space

Question

We have a D drive that has 999 GB of total space. Currently the production database data files are being stored there. It is running out of space.

A few weeks ago DB files were shrunk to temporarily free up some space. At first there was 51.9 GB of free space left. After shrinking, there was 80.9 GB free space. As of this morning, there is 72.6 GB free, so the files grew 8.3 GB.

There is another drive (G: drive) that we have which is storing 46 DB data files. G: drive 481GB free out of 999 GB. So I was thinking that I can possibly move some of the DB files to from D: drive to G: drive. If I do this option, then it would be best to wait during off hours since the DB's need to be altered to go into offline mode. Again, this would be a temporary fix. First question is: how would I know which data file would be best to move to the G: drive?

Eventually I would need to request additional drive space. This can take up to 2 weeks to process. My second question is: how do I know how much space to request? Is there a simple formula I can use to calculate?

Please let me know your thoughts/advice . Thank you in advance.

Answer 1

how would I know which data file would be best to move to the G: drive?

You could decide based on:

• The latency for each database in order to balance the read/write so that none of the two disks would be overloaded. See Monitoring Read/Write Latency
• How critical is the database to choose the ones that would allow the longer time offline for the maintenance without users complaining and keeping your SLA.
• How big is the database to free the more space moving less databases by choosing the bigger ones.

how do I know how much space to request? Is there a simple formula I can use to calculate?

I don't believe there's a right formula, but the basic idea is to analyze historical usage (the more you have the better) to estimate future needs. But it's an estimation and a new project could go to production next month with needs your historical data could not have guessed. See How to Check Monthly Growth of Database in SQL Server if you don't already have a proper system gathering your growth rate.

Even that 8.3 GB could be used to estimate your growth rate, but the shorter the period you collected that info the less precise your estimation will be.

One advice: if you're acquiring physical disks, it might be worth to have 2 500Gb disks instead of 1 1Tb disk (assuming you estimated 1Tb would be enough to last the period you decided to be appropriate). The reason is that placing the files in different disks is a way of reducing latency.