Skip to content

Find total and free disk space from SQL Server

You can use below query to find the total disk space of each drive in the server and total free space availble for each disk. This script will be handy for level 2 DBA’s who don’t have direct access to database server and perform their duties by connecting through SQL Server Management Studio (SSMS) from a jump server.

Here are the two steps that you have to follow. Step 1 can be ignored if xp_cmdshell is already enabled for your instance.

1) Enable xp_cmdshell if not already enabled.

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
go
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2) Execute below script to collect the required details.

Declare @Used_Bytes Varchar(1000),
@Free_Bytes Varchar(1000),
@TotalBytes BIGINT,
@IDENTITY INT,
@Drive Char(1),
@sql Varchar(1000)

SET NOCOUNT ON
Create table ##DiskSpace ( Drive Char(1), Total_Space Bigint, Free_Space Bigint,
Percentage_Free as (Free_Space*100 / Total_Space ) )
Create table #Fixeddrives ( Drive Char(1), Free_Space Bigint)
create table ##Dir ( ID INT IDENTITY , Drive_Size Varchar(2000))

Insert into #Fixeddrives exec master.dbo.xp_fixeddrives
insert into ##DiskSpace ( Drive , Free_Space)
select Drive , Free_Space from #Fixeddrives

DECLARE Drive_cursor CURSOR FOR
SELECT Drive from ##DiskSpace
OPEN Drive_cursor
FETCH NEXT FROM Drive_cursor INTO @Drive

WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = 'insert into ##Dir exec master.dbo.xp_cmdshell ''dir '+ @Drive+': /S /A /-C'''
exec(@sql)
SELECT @IDENTITY = @@IDENTITY
delete from ##Dir where ID < @IDENTITY - 4 select @Used_Bytes = substring (Drive_Size, charIndex ('File(s)', Drive_Size, 0)+ 9 , 1000) from ##Dir where Drive_Size like '%File(s)%' while patindex('%[^0-9]%', @Used_Bytes) > 0
begin
set @Used_Bytes = stuff( @Used_Bytes, patindex('%[^0-9]%', @Used_Bytes), 1, '' )
end

select @Free_Bytes = substring (Drive_Size, charIndex ('Dir(s)', Drive_Size, 0)+ 9 , 1000)
from ##Dir where Drive_Size like '%Dir(s)%'

while patindex('%[^0-9]%', @Free_Bytes) > 0
begin
set @Free_Bytes = stuff( @Free_Bytes, patindex('%[^0-9]%', @Free_Bytes), 1, '' )
end

select @TotalBytes = Convert(bigint, @Used_Bytes)+ Convert(bigint, @Free_Bytes)
select @TotalBytes = (@TotalBytes/ 1024)/1024
Update ##DiskSpace set Total_Space = @TotalBytes
WHERE Drive = @Drive

TRUNCATE TABLE ##Dir
FETCH NEXT FROM Drive_cursor INTO @Drive

END
CLOSE Drive_cursor
DEALLOCATE Drive_cursor

select * from ##DiskSpace

drop table ##DiskSpace
drop table #Fixeddrives
drop table ##Dir