T-SQL to Get Drive Space Info

Problem


I was trying to get disk information like total space and free using t-sql but extended stored procedure xp_fixeddrives provide only free space info(see screen shot below).

Solution

Powershell can fetch disk information very easily. T-SQL script below uses powershell to get this information.

Pre-requisite:  xp_cmdshell  and Powershell 1.0

To enable xp_cmdshell  execute the following script.

sp_configure 'show advanced options', 1
Go 
Reconfigure 
Go 
sp_configure 'xp_cmdshell',1 
go 
Reconfigure 
Go

Create new store procedure sp_spaceinfo by executing following script.


USE [master]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_spaceinfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_spaceinfo]
GO


USE [master]
GO


CREATE PROCEDURE [dbo].[sp_spaceinfo] 
AS


DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;
INSERT INTO @psinfo
EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;
DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';
update @psinfo set data = REPLACE(data,' ',',');


;With DriveSpace as (


select SUBSTRING(data,1,2)  as [Drive], 
replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
(substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
as [FreeSpace]
, 
replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%', 
(substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
as [Size]
from @psinfo
) 
SELECT Drive, convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) as FreeSpaceGB, convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as SizeGB
FROM DriveSpace;
GO

OS output

sp_spaceused output

Tested On

I have tested this code on SQL Server 2008 R2 , SQL Server 2008 and SQL Server 2005.
Leave you feedback for encouragement.