However, the command output doesn’t include the total size of each drive, making it impossible to determine the percent free space. If you’re in an environment where a separate team monitors disk space, and has alerts set when free space falls below a certain percentage, you may want to ensure you don’t breach those levels. The following script provides “the big picture” for your servers, since it provides total size, free space, available space, and the percent free. It does require the use of the documented and supported
sys.xp_cmdshellsystem extended stored procedure. The code uses the drive letters returned by
sys.xp_fixeddrivesinside a cursor. Inside the cursor, we call the dos command
fsutil volume diskfree C:to get total capacity and free space, etc:
Click through for the script.