Sql-server – How to get the Total disk space in ms sql 2008 or below

sql serversql-server-2008

I am using ms sql 2008, I wanted to get the total space and available free space on my drives.I used xp_fixeddrives. Unfortunately I got only free disk Space.So does anyone have any other solution so as to get both total disk space and available disk space in ms sql 2008 or below?

CREATE TABLE #drives (
    drive char,
    [free] int
)

INSERT INTO #drives
EXEC master..xp_fixeddrives

For ms sql 2008 R2 and above I can get the total drive space using this query:

WITH core AS ( 
    SELECT DISTINCT
        s.volume_mount_point [Drive],
        CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs]
    FROM 
        sys.master_files f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)

Best Answer

Since you mentioned you are already using xp_fixeddrives,following code will give you total size. Many shops will not allow this for security reason. I copied the code from here.

There are few more version of this code and example of psinfo.

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) 
DECLARE @MB Numeric ; 
SET @MB = 1048576
CREATE TABLE #drives 
    (drive char(1) PRIMARY KEY, 
     FreeSpace int NULL,
     TotalSize int NULL) 

INSERT #drives(drive,FreeSpace) 

EXEC master.dbo.xp_fixeddrives 

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
IF @hr <> 0 
EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', 
@TotalSize OUT IF @hr <> 0 

EXEC sp_OAGetErrorInfo @odrive 

UPDATE #drives SET TotalSize=@TotalSize/@MB 
WHERE  drive=@drive 
FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT
 drive, 
 TotalSize as 'Total(MB)', 
 FreeSpace as 'Free(MB)' 
FROM #drives
ORDER BY drive 
DROP TABLE #drives 
GO
Related Question