T-SQL Query to find Drive space details | SansSQL

Thursday, March 28, 2013

T-SQL Query to find Drive space details

Here is a T-SQL Query to find Drive space details.
This query returns Total drive space, Used space on the drive, Free space on the drive along with the free space percentage.

This requires, enabling xp_cmdshell

Enable xp_cmdshell:
EXEC sp_configure 'show advanced options' , 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell' , 1
GO
RECONFIGURE
GO

Once xp_cmdshell is enabled, executing the below script will provide the details about the drives of the server that hosts the connected SQL Server Instance
 DECLARE @MOUNTVOL TABLE
 ( MOUNTVOLResult nVARCHAR(500)
  ,ExecCommand nVARCHAR(500))
 
INSERT INTO @MOUNTVOL (MOUNTVOLResult) 
EXEC XP_CMDSHELL 'MOUNTVOL'
 
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%VOLUME%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult IS NULL
DELETE @MOUNTVOL WHERE MOUNTVOLResult NOT LIKE '%:%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%MOUNTVOL%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%RECYCLE%'
 
UPDATE @MOUNTVOL SET ExecCommand = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + LTRIM(RTRIM(MOUNTVOLResult)) +''''
 
DECLARE @DRIVESpace TABLE
 ( DriveLetter VARCHAR(10)
  ,DriveInfo VARCHAR(100))
   
WHILE (SELECT COUNT(*) FROM @MOUNTVOL) <>0
BEGIN
 DECLARE @Command nVARCHAR(500), @DriveLetter nVARCHAR(10)
 Select @Command = ExecCommand, @DriveLetter= MOUNTVOLResult from @MOUNTVOL
 INSERT INTO @DRIVESpace (DriveInfo) Exec sp_executeSQL @Command
 UPDATE @DRIVESpace SET DriveLetter=@DriveLetter WHERE DriveLetter IS NULL
 DELETE FROM @MOUNTVOL WHERE ExecCommand=@Command
END
 
DECLARE @FinalResults TABLE
 ( DriveLetter nVARCHAR(10)
  ,[TotalDriveSpace(MB)] DECIMAL(18,2)
  ,[UsedSpaceOnDrive(MB)] AS ([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])
  ,[FreeSpaceOnDrive(MB)] DECIMAL(18,2)
  ,[TotalDriveSpace(GB)] AS CAST(([TotalDriveSpace(MB)]/1024) AS DECIMAL(18,2))
  ,[UsedSpaceOnDrive(GB)] AS CAST((([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])/1024) AS DECIMAL(18,2))
  ,[FreeSpaceOnDrive(GB)] AS CAST(([FreeSpaceOnDrive(MB)]/1024) AS DECIMAL(18,2))
  ,[%FreeSpace] AS CAST((([FreeSpaceOnDrive(MB)]/[TotalDriveSpace(MB)])*100) AS DECIMAL(18,2)))
 
INSERT INTO @FinalResults (DriveLetter, [TotalDriveSpace(MB)],[FreeSpaceOnDrive(MB)])
SELECT RTRIM(LTRIM(DriveLetter))
    ,[TotalDriveSpace(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
    ,[FreeSpaceOnDrive(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF FREE BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
FROM @DRIVESpace
WHERE DriveInfo LIKE 'TOTAL # OF %'
GROUP BY DriveLetter
ORDER BY DriveLetter
 
SELECT * FROM @FinalResults


5 comments:

Unknown said...

how to show drive space in the form of GB's

Sandesh Segu said...

Script has been updated to show values in GB as well.

Unknown said...

Very useful. Thank you

Bhavin said...

how to add drive name in this ?

Unknown said...

Am I the only one who receiving this message?
(1 row affected)
Msg 8114, Level 16, State 5, Line 44
Error converting data type varchar to float.

Post a Comment

Ads