How to save some Emergency Drive Space for SQL Server

Running out of disk space on a SQL Server server is something that you should never allow to happen, but it can happen.

If you need to be the emergency Fireman putting out the fire, here is a way to “reserve” some drive space ahead of time. It is literally an 8GB “dummy” file that you can delete at any time without worry – nothing is using the file. Just SHIFT+DELETE it so it doesn’t fill up the Recycle Bin.

fsutil.exe file createnew "Emergency Disk Space (safe to delete).$$$" 8589934592

This will create an 8GB temporary file in the current folder. Adjust the size to suit your future needs.

When you need to reclaim the disk space, simply select the file and shift+delete (no need to send it to the Recycle Bin).

Here’s the gist if you don’t feel like copy & pasting.

TempDB Best Practices

  • TempDB should have as many data files as there are cores available to SQL Server, but only up to 8 data files. Too many TempDB data files can cause contention. Recommendation: Create 8 files and test from there on.
  • TempDB should only have 1 log file. Extra log files will improve performance as they are written to sequentially.
  • The data files for TempDB should all be the same size.
  • TempDB data files and TempDB log files should be on separate disks.
  • TempDB files should be on the fastest disks.