Im writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. Ive used WMI script to do this, Copy the vbs script and save it to a location as diskspace.vbs and use this location in the sql script to get the result.
SQL Script
SET NOCOUNT ON IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp') DROP TABLE ##tmp CREATE TABLE ##tmp(diskspace VARCHAR(200)) INSERT ##tmp EXEC master.dbo.xp_cmdshell 'cscript C:\diskspace.vbs' --change the PATH here SET ROWCOUNT 3 DELETE ##tmp SET ROWCOUNT 0 IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2') DROP TABLE ##tmp2 CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT) INSERT ##tmp2 SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)), CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL SELECT * FROM ##tmp2
VBS Script
Const HARD_DISK = 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\" & strComputer & "rootcimv2")
Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")
For Each objDisk in colDisks
Wscript.Echo objDisk.DeviceID & " " & mid((objDisk.size)/1048576,1,10) & " " & mid((objDisk.Freespace)/1048576,1,10)
Next
Sample Output
| Driver Letter | TotalDiskSpace_in_MB | Freespace_in_MB |
| C: | 12644.8789 | 1153.70312 |
| D: | 45504.3945 | 29603.9921 |
| E: | 10001.371 | 450.316406 |
Leave a Reply