SQL-Articles
On Premise and Cloud Database Knowledge Base

Disk space Check via SQL Server

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*