How to get the Disk Space in SQL Server?
There are 2 ways
a. xp_fixeddrives — The Easy way
b. Ftsutil(xp_cmdshell) — If I want to know the percentage of free space in harddisk use FTSUTIL . The only thing you need to enable is the xp_cmdshell.
Click here to read how to enable XP_cmdshell
Here is the proc
CREATE PROCEDURE [dbo].[usp_GetDiskSpace]
– Add the parameters for the stored procedure here
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)
SET @Drive = 97
– Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
TRUNCATE TABLE Freediskspace
WHILE @Drive < = 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC (@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @Drive = @Drive + 1
END
-- Show the expected output
INSERT INTO FreeDiskSpace
(
Drive
,TotalGB
,FreeGB
)
SELECT Drive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS FLOAT) ELSE CAST(0 AS BIGINT) END)/1073741824 AS TotalGB,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS floaT) ELSE CAST(0 AS BIGINT) END)/1073741824 AS FreeGB
FROM (
SELECT Drive,
Info
FROM @Drives
WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive
UPDATE FreeDiskSpace
SET PercentageGB = CONVERT(DECIMAL(5,2),ROUND(CONVERT(DECIMAL(7,2),ROUND(FreeGB,2)) * 100 / CONVERT(DECIMAL(7,2),ROUND(TotalGB,2)),2))
WHERE PercentageGB IS null
SELECT
drive
,CONVERT(DECIMAL(7,2),ROUND(TotalGB,2)) AS TotalDiskSpace
,CONVERT(DECIMAL(7,2),ROUND(FreeGB,2)) AS FreeSpace
,PercentageGB
FROM FreeDiskSpace
END
GO
