Drives, with Capacity and Free Space

A typical part of the day for many DBAs includes managing available database capacity. Widely accepted best practices include setting a reasonable limit on maximum file sizes, for both data and log files. When a database or log file is near to consuming the maximum available space, you’ll need to either delete or archive some data, or expand the data or log file to allow for future growth. If you don’t have easy access to the SQL Server itself, it can be somewhat challenging to get an accurate picture of the drive sizes and free space available.

Vincent Van Gogh - Starry Night...

Vincent Van Gogh – Starry Night…

The undocumented command, sys.xp_fixeddrives provides a quick method of listing drive letters, along with the number of free megabytes.

Output looks like:

╔═══════╦═════════╗
║ drive ║ MB free ║
╠═══════╬═════════╣
║ C     ║   62172 ║
║ D     ║   69905 ║
║ E     ║   69219 ║
║ F     ║  120959 ║
║ G     ║    4038 ║
╚═══════╩═════════╝

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_cmdshell system extended stored procedure. The code uses the drive letters returned by sys.xp_fixeddrives inside a cursor. Inside the cursor, we call the dos command fsutil volume diskfree C: to get total capacity and free space, etc:

╔═══════╦══════════╦═══════════════╦════════════════════╦══════════════╗
║ drive ║ Total MB ║ Total Free MB ║ Total Available MB ║ Percent Free ║
╠═══════╬══════════╬═══════════════╬════════════════════╬══════════════╣
║ C:    ║  101,897 ║        62,172 ║             62,172 ║ 61.01%       ║
║ D:    ║  102,396 ║        69,905 ║             69,905 ║ 68.27%       ║
║ E:    ║  266,237 ║        69,219 ║             69,219 ║ 26.00%       ║
║ F:    ║  573,437 ║       120,959 ║            120,959 ║ 21.09%       ║
║ G:    ║   12,284 ║         4,038 ║              4,038 ║ 32.88%       ║
╚═══════╩══════════╩═══════════════╩════════════════════╩══════════════╝

Let me know on Twitter what you think about this script, or if you have any questions.

Check out the rest of our SQL Server Tools.

Ads by Google, Paying the Rent: