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:
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:
how to show drive space in the form of GB's
Script has been updated to show values in GB as well.
Very useful. Thank you
how to add drive name in this ?
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