Finding disk occupancy details from SQL Server
To find disk space details we have straight forward extended procedure which gives details very quickly.
That is XP_fixeddrives stored procedure which gives the below output.
To get a better picture so that you can present the output directly to business or users, please use the below code which shows output in much fashion.
Ensure below step is performed first.
Use master
go
sp_configure 'xp_cmdshell',1
go
reconfigure
go
Actual code for finding the disk space details.
create table #temp1 (Drive Char(1),[MB Free] bigint,[MB Total] bigint)
insert into #temp1(Drive,[MB Free]) exec master..xp_fixeddrives
Declare @var varchar(1)
Declare @sql nvarchar(1000)
Declare @c1 cursor
set @c1 = CURSOR For select Drive from #temp1
open @c1
fetch next from @c1 into @var
while @@fetch_status =0
Begin
create table #temp2(output nvarchar(100))
set @sql = 'insert #temp2 exec master..xp_cmdshell ''fsutil volume diskfree '+ @var +':'''
execute sp_executesql @sql
update #temp1
set [MB Total] = (select cast((SUBSTRING(output,Len(LEFT(output,CHARINDEX(':',output)))+1,LEN(output))) as bigint)/1024/1024 as Total_space_MB from #temp2 where output is not null
and output like 'Total # of bytes%')
where Drive = @var
drop table #temp2
fetch next from @c1 into @var
End
Close @c1
Deallocate @c1
Select Drive,[MB Free],[MB Total],cast(([MB Free]*100/[MB Total]) as decimal(4,2)) 'Percent of Free Space' from #temp1
Drop table #temp1
insert into #temp1(Drive,[MB Free]) exec master..xp_fixeddrives
Declare @var varchar(1)
Declare @sql nvarchar(1000)
Declare @c1 cursor
set @c1 = CURSOR For select Drive from #temp1
open @c1
fetch next from @c1 into @var
while @@fetch_status =0
Begin
create table #temp2(output nvarchar(100))
set @sql = 'insert #temp2 exec master..xp_cmdshell ''fsutil volume diskfree '+ @var +':'''
execute sp_executesql @sql
update #temp1
set [MB Total] = (select cast((SUBSTRING(output,Len(LEFT(output,CHARINDEX(':',output)))+1,LEN(output))) as bigint)/1024/1024 as Total_space_MB from #temp2 where output is not null
and output like 'Total # of bytes%')
where Drive = @var
drop table #temp2
fetch next from @c1 into @var
End
Close @c1
Deallocate @c1
Select Drive,[MB Free],[MB Total],cast(([MB Free]*100/[MB Total]) as decimal(4,2)) 'Percent of Free Space' from #temp1
Drop table #temp1
Above code will give the below output.
