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…
The undocumented command, sys.xp_fixeddrives provides a quick method of listing drive letters, along with the number of free megabytes.
|
1 |
EXEC sys.xp_fixeddrives; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
DROP TABLE IF EXISTS #driveinfo; DROP TABLE IF EXISTS #drives; GO DECLARE @drive varchar(100); CREATE TABLE #driveinfo ( rn int IDENTITY(1,1) PRIMARY KEY CLUSTERED , drive varchar(100) NULL , txt varchar(1000) NULL , item varchar(100) NULL , quantity bigint NULL ); CREATE TABLE #drives ( drive varchar(100) NOT NULL , [MB free] bigint NOT NULL ); INSERT INTO #drives (drive, [MB free]) EXEC sys.xp_fixeddrives; DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT d.drive FROM #drives d ORDER BY d.drive; OPEN cur; FETCH NEXT FROM cur INTO @drive; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @cmd varchar(1000); SET @cmd = 'fsutil volume diskfree ' + @drive + ':'; INSERT INTO #driveinfo (txt) EXEC sys.xp_cmdshell @cmd; UPDATE #driveinfo SET drive = @drive + ':' WHERE drive IS NULL; FETCH NEXT FROM cur INTO @drive; END CLOSE cur; DEALLOCATE cur; DELETE di FROM #driveinfo di WHERE di.txt IS NULL; UPDATE di SET di.item = LEFT(di.txt, CHARINDEX(':', di.txt) - 1) , di.quantity = RIGHT(di.txt, CHARINDEX(':', REVERSE(di.txt)) - 1) FROM #driveinfo di ALTER TABLE #driveinfo DROP COLUMN txt; ;WITH src AS ( SELECT pvt.drive , pvt.[Total # of bytes ] , pvt.[Total # of free bytes ] , pvt.[Total # of avail free bytes ] FROM #driveinfo PIVOT ( SUM(quantity) FOR item IN ( [Total # of free bytes ] , [Total # of bytes ] , [Total # of avail free bytes ] ) ) pvt ) SELECT src.drive , [Total MB] = FORMAT(SUM(src.[Total # of bytes ] / 1048576), '#,###') , [Total Free MB] = FORMAT(SUM(src.[Total # of free bytes ] / 1048576), '#,###') , [Total Available MB] = FORMAT(SUM(src.[Total # of avail free bytes ] / 1048576), '#,###') , [Percent Free] = FORMAT(CONVERT(decimal(38,0), SUM(src.[Total # of free bytes ])) / CONVERT(decimal(38, 0), SUM(src.[Total # of bytes ])), '0.00%') FROM src GROUP BY src.drive |
╔═══════╦══════════╦═══════════════╦════════════════════╦══════════════╗ ║ 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.
Hi,
very usefull, so because some of customers Don’t run SQL Server 2016, i think it’s mandatory to check SQL Server Edition and adapt the Drop Table command
DROP TABLE IF EXISTS #driveinfo;
DROP TABLE IF EXISTS #drives;
Hi Eric,
If you need to run this script on versions of SQL Server prior to 2016, you could use the following replacements for the
DROP TABLE IF EXISTScommands:I hope that helps!
[…] Hannah Vernon has a script which shows more than what xp_fixeddrives has to offer: […]
Modification to the UPDATE on line 49 to account for when the server sometimes returns a “(#GB)” tag as part of the free space description:
UPDATE di
SET di.item = LEFT(di.txt, CHARINDEX(':', di.txt) - 1),
di.quantity = CASE CHARINDEX('(', di.txt) --account for when fsutil returns "###(XGB)" label
WHEN 0 THEN
RIGHT(di.txt, CHARINDEX(':', REVERSE(di.txt)) - 1) --"###" format
ELSE
SUBSTRING(
di.txt,
CHARINDEX(':', di.txt) + 1,
CHARINDEX('(', di.txt) - CHARINDEX(':', di.txt) - 2
) --"###(XGB) format
END
FROM #driveinfo di
Further modification for when SQL Server does not have access to some of the drives and you get an “Error: Access is Denied” from fsutil…