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 bring 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 drive 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

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

Well, how would we know? I would look into if there are any databases that can permit downtime during office hours to facilitate the move. If there is no distinction that way, or such databases are too small to be worth it, I might take the database that seems to grow the fastest.

My second question is: how do I know how much space to request? Is there a simple formula I can use to calculate?

Yes, the formula is "more". No, matter how much you get., the answer is always "more".

More [sic!] seriously, this depends so much on the workload that there not a ready-made formula you can use. But from what you have told us, I say that you need at least 2TB more of disk space.

Answer 2

Hi,

>First question is: how would I know which data file would be best to move to the G: drive?*

You may want to find the database with the fastest data file size growth/use more space. Just need to monitor the usage and growth of the database.

>My second question is: how do I know how much space to request? Is there a simple formula I can use to calculate?

This definitely depends on your actual workload and space usage requirements. Monitor for a period of time and then speculate on possible peaks. Good database maintenance (regular index rebuilding, log backups, etc.) helps to reuse space and effectively shrink the database.