Determine Server Disk Space with T-SQL

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s