legacy code and content moved from my previous blog site
Problem
T-SQL code to obtain available disk space, free disk space out of total disk space. The script obtains the result for both NTFS local drives and NTFS mounted drives i.e. drives mounted as folder(s).
This was my first script that I ever wrote and has been running successfully till date in a client production environment. Way back in 2012 my manager asked me to provide a Service improvement plan for one of our clients. The basic idea was to monitor the drives space for drives having database files i.e. ‘.mdf’, ‘.ndf’ and ‘.ldf’ without using any external monitoring tool. I know there are better ways to do it using Python or PowerShell but back then knew either so T-SQL is what I came up with.
This script uses advanced options which could be enabled in few servers and disabled on the others. Client won’t allow us to change the original settings for each and every server.
Case 1: Advanced Options Disabled. Script enables the advanced options in SQL Server on its execution and revert them back after generating the output.
Case 2: Advanced Options Enabled. Script will execute without messing up the advanced options.
We created a standard script which took care of the both of the above said cases. Now then we integrated it with our monitoring tool and we really impressed the client with the alerts which were generated for monitoring.
Solution
The following code works for local disk and mounted disks. This code has compatibility to work with version SQL Server 2005 and later.
/*CODED AND TESTED BY SANDEEP ARORA [hello@sandeep-arora.com]*/
/*Version 1.0.0.DMX*/
/*I am not maintaining this script as this is legacy code so there will be no more versions going forward*/
------------------------------------------------
------------------------------------------------
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
------------------------------------------------
SET NOCOUNT ON
create table #spconfig(s_name varchar(50), mini bigint, maxi bigint, config_value bigint, run_value bigint)
insert into #spconfig(s_name, mini, maxi,config_value, run_value) execute sp_configure
declare @testvaluec as int
declare @testvaluer as int
set @testvaluec = (select config_value from #spconfig where s_name like '%xp_cmdshell%' )
set @testvaluer = (select run_value from #spconfig where s_name like '%xp_cmdshell%' )
---------------------------------------------------------------------------------------------------------------
---------------------------------------------CONDITION 1
IF (@testvaluec =0 AND @testvaluer = 0)
----------------------------------------------BLOCK A
BEGIN
---------------------------------------------------
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
exec sp_configure 'xp_cmdshell', 1;
RECONFIGURE with override;
set nocount on
create table #tempvolume(line varchar(100), vol_size varchar(100), vol_free_size varchar(100),type varchar(5))
create table #tempsize(mysize varchar(100))
create table #testvol2a(output1 varchar(200))
insert into #testvol2a exec master..xp_cmdshell 'mountvol \L | findstr :\\'
create table #test2dropa(output1 varchar(200))
insert into #test2dropa select LTRIM(output1)from #testvol2a
insert into #tempvolume(line) select LEFT(output1, LEN(output1) - 1) from #test2dropa;
--select * from #tempvolume1
----------------------------------------------------------
drop table #test2dropa
drop table #testvol2a
----------------------------------------------------------
declare @dir_name varchar(400)
declare @cmd_line varchar(100)
declare @line_cnt int
declare db_cursor1 CURSOR FOR select line from #tempvolume where len(ltrim(line))>=1
open db_cursor1
fetch next from db_cursor1 into @dir_name
while @@FETCH_STATUS =0
BEGIN
select @cmd_line='fsutil volume diskfree "' + @dir_name + '" | findstr "Total"'
--Print @cmd_line
truncate table #tempsize
insert into #tempsize execute master..xp_cmdshell @cmd_line
select @line_cnt=@@rowcount
if @line_cnt > 0
BEGIN
update #tempvolume set type='VOL', vol_size= substring(s.mysize, charindex(':',s.mysize) + 1,50) from #tempsize s, #tempvolume v where v.line=@dir_name and charindex('of bytes',s.mysize) > 0
update #tempvolume set vol_free_size=substring(s.mysize, charindex(':',s.mysize) + 1,50) from #tempsize s, #tempvolume v where v.line=@dir_name and charindex('of free bytes',s.mysize) > 0
END
fetch next from db_cursor1 into @dir_name
END
create table #temp_test (drive varchar(400) ,
TotalSize int , FreeSpace int , Freespacepercent int)
insert into #temp_test
select ltrim(line) as 'VOLUME NAME',
case type when 'VOL' then convert(int,convert(float,ltrim(vol_size))/1048576) else convert(int,vol_size) end as 'TOTAL SIZE (GB)' ,
case type when 'VOL' then convert(int,convert(float,ltrim(vol_free_size))/1048576) else convert(int,vol_free_size) end as 'FREE SPACE( GB)',
case type when 'VOL' then convert(int,convert(float,ltrim(vol_free_size))/1048576)*100/convert(int,convert(float,ltrim(vol_size))/1048576)
else convert(int,vol_free_size)*100/convert(int,vol_size) end as 'FREE SPACE (%)'
from #tempvolume where vol_size is not null
order by line
create table #temp_test2 (drive varchar(400) ,
TotalSize int , FreeSpace int , Freespacepercent int)
insert into #temp_test2 SELECT drive,TotalSize,FreeSpace,Freespacepercent FROM #temp_test,sys.sysaltfiles WHERE sys.sysaltfiles.filename LIKE #temp_test.drive + '%.mdf' OR sys.sysaltfiles.filename LIKE #temp_test.drive + '%.ldf'
select distinct (drive) as 'DRIVE NAME', cast((cast(TotalSize as decimal(11,2))/1024) as decimal(11,2)) as 'TOTAL DISK SPACE (GB)', cast((cast(FreeSpace as decimal(11,2))/1024) as decimal(11,2)) as 'FREE SPACE AVAILABLE (GB)' , Freespacepercent as 'FREE (%)' from #temp_test2
close db_cursor1
deallocate db_cursor1
drop table #temp_test
drop table #temp_test2
drop table #tempvolume
drop table #tempsize
---------------------------------------------------------------
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
exec sp_configure 'xp_cmdshell', 0;
RECONFIGURE with override;
exec sp_configure 'show advanced options', 0;
Reconfigure with override;
----------------------------------------------------------------
Drop Table #spconfig
END
----------------------------------------------------------------
---------------------------------------------CONDITION 2
ELSE IF (@testvaluec =1 AND @testvaluer = 1)
----------------------------------------------------------BLOCK B
BEGIN
-------------------------------------------------------------------
set nocount on
create table #tempvolume1(line varchar(200), vol_size varchar(100), vol_free_size varchar(100),type varchar(5))
create table #tempsize1(mysize varchar(100))
create table #testvol2(output1 varchar(200))
insert into #testvol2 exec master..xp_cmdshell 'mountvol \L | findstr :\\'
create table #test2drop(output1 varchar(200))
insert into #test2drop select LTRIM(output1) from #testvol2
insert into #tempvolume1(line) select LEFT(output1, LEN(output1) - 1) from #test2drop;
--select * from #tempvolume1
----------------------------------------------------------
drop table #test2drop
drop table #testvol2
----------------------------------------------------------
declare @dir_name1 varchar(400)
declare @cmd_line1 varchar(100)
declare @line_cnt1 int
declare db_cursor CURSOR FOR select line from #tempvolume1 where len(ltrim(line))>=1
open db_cursor
fetch next from db_cursor into @dir_name1
while @@FETCH_STATUS =0
BEGIN
select @cmd_line1='fsutil volume diskfree "'+ @dir_name1+ '" | findstr "Total"'
--Print @cmd_line1
truncate table #tempsize1
insert into #tempsize1 execute master..xp_cmdshell @cmd_line1
select @line_cnt1=@@rowcount
if @line_cnt1 > 0
BEGIN
update #tempvolume1 set type='VOL', vol_size= substring(s.mysize, charindex(':',s.mysize) + 1,50) from #tempsize1 s, #tempvolume1 v where v.line=@dir_name1 and charindex('of bytes',s.mysize) > 0
update #tempvolume1 set vol_free_size=substring(s.mysize, charindex(':',s.mysize) + 1,50) from #tempsize1 s, #tempvolume1 v where v.line=@dir_name1 and charindex('of free bytes',s.mysize) > 0
END
fetch next from db_cursor into @dir_name1
END
create table #temp_test1 (drive varchar(400) ,
TotalSize int , FreeSpace int , Freespacepercent int)
insert into #temp_test1
select ltrim(line) as 'VOLUME NAME',
case type when 'VOL' then convert(int,convert(float,ltrim(vol_size))/1048576) else convert(int,vol_size) end as 'TOTAL SIZE (GB)' ,
case type when 'VOL' then convert(int,convert(float,ltrim(vol_free_size))/1048576) else convert(int,vol_free_size) end as 'FREE SPACE( GB)',
case type when 'VOL' then convert(int,convert(float,ltrim(vol_free_size))/1048576)*100/convert(int,convert(float,ltrim(vol_size))/1048576)
else convert(int,vol_free_size)*100/convert(int,vol_size) end as 'FREE SPACE (%)'
from #tempvolume1 where vol_size is not null
order by line
create table #temp_test12 (drive varchar(400) ,
TotalSize int , FreeSpace int , Freespacepercent int)
insert into #temp_test12 SELECT drive,TotalSize,FreeSpace,Freespacepercent FROM #temp_test1,sys.sysaltfiles WHERE sys.sysaltfiles.filename LIKE #temp_test1.drive + '%.mdf' OR sys.sysaltfiles.filename LIKE #temp_test1.drive + '%.ldf'
select distinct (drive) as 'DRIVE NAME', cast((cast(TotalSize as decimal(11,2))/1024) as decimal(11,2) )as 'TOTAL DISK SPACE (GB)', cast((cast(FreeSpace as decimal(11,2))/1024) as decimal(11,2)) as 'FREE SPACE AVAILABLE (GB)' , Freespacepercent as 'FREE (%)' from #temp_test12
close db_cursor
deallocate db_cursor
drop table #temp_test1
drop table #temp_test12
drop table #tempvolume1
drop table #tempsize1
--------------------------------------------------------
exec sp_configure 'show advanced options', 0;
Reconfigure with override;
---------------------------------------------------------
drop table #spconfig
END
Sample Output
The 'Cloud' Workbook 