Archive for the ‘Disk Drives Space and Utilization Information’ Category

How to query and display the disk drives space and utilization information?

Have you ever wondered how to query and display the disk drives space and utilization information? By utilizing the OLE Automation Objects in TSQL, now you can. You can read more about OLE Automation Objects here: http://technet.microsoft.com/en-us/library/ms189933.aspx. You will need to enable ‘ole automation procedures’ before you can use it.
The following is the code to show the:

  • Drive
  • Drive Label
  • Total Capacity MB
  • Space Used MB
  • Free Space MB
  • % Free

It is critical to note that it does not work for mounted volumes. If you are not sure what a mounted volume is then probably you don’t need to concern about it yet. When you figured out on how to modify to get the following code work for mounted volumes, please share the code with me.

Here we go:

-- disk drives space and usage info

-- turn on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin

	declare @oleautomationprocedures bit
	declare @isshowadvancedoptions bit
	
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	
	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'ole automation procedures', if not then enable it
	select @oleautomationprocedures = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'ole automation procedures' ;
	if @oleautomationprocedures = 0
	begin
		exec sp_configure 'ole automation procedures', 1;
		reconfigure;
	end
	
end

set nocount on
declare @hr int;
declare @fso int;
declare @driveletter char(1);
declare @odrive int;
declare @totalsize varchar(20);
declare @volumename nvarchar(256);
declare @mb numeric; 
set @mb = 1048576;
set @driveletter = '';

if object_id('tempdb..#fixeddrives') is not null
	drop table #fixeddrives;

create table #fixeddrives (
	[Drive]	varchar(10) primary key, -- drive letter
	[Drive Label] nvarchar(256), -- drive label
	[Total Capacity MB] numeric(10,2), -- total capacity of the drive in mb
	[Space Used MB] numeric(10,2), -- space used in mb
	[Free Space MB] numeric(10,2), -- free space in mb
	[% Free] numeric(10,2) -- percentage free as of now
);

insert into #fixeddrives([Drive], [Free Space MB]) 
exec master..xp_fixeddrives;

exec @hr=sp_OACreate 'scripting.filesystemobject',@fso out 
if @hr <> 0 exec sp_oageterrorinfo @fso

while exists (select 1 from #fixeddrives d where d.Drive > @driveletter)
begin
	select top 1 @driveletter = d.Drive 
	from #fixeddrives d 
	where d.Drive > @driveletter 
	order by d.Drive asc

	exec @hr = sp_OAMethod @fso,'getdrive', @odrive out, @driveletter; 
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	exec @hr = sp_OAGetProperty @odrive,'totalsize', @totalsize out;
		if @hr <> 0 exec sp_oageterrorinfo @odrive;

	exec @hr = sp_OAGetProperty @odrive,'volumename', @volumename out;
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	update #fixeddrives 
	set [Total Capacity MB]= @totalsize/@mb ,
			[Drive Label]= @volumename
	where
		Drive = @driveletter; 
		
end

exec @hr=sp_OADestroy @fso; 

if @hr <> 0 
exec sp_oageterrorinfo @fso;

update #fixeddrives 
set [Space Used MB]= [Total Capacity MB] - [Free Space MB],
		[% Free] = [Free Space MB]* 100/[Total Capacity MB];

select [Drive]
, [Drive Label]
, [Total Capacity MB]
, [Space Used MB]
, [Free Space MB]
, [% Free]
from #fixeddrives		

-- revert the option on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'ole automation procedures') <> @oleautomationprocedures
	begin
		exec sp_configure 'ole automation procedures', @oleautomationprocedures;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end
	
end

In case you wish to send a report via email for the disk drives space and utilization information, this is the code:

-- disk drives space and usage info
/*NOTE: Replace the recipients email addresses in line #128 */

set nocount on
-- turn on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin

	declare @oleautomationprocedures bit
	declare @isshowadvancedoptions bit
	
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	
	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'ole automation procedures', if not then enable it
	select @oleautomationprocedures = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'ole automation procedures' ;
	if @oleautomationprocedures = 0
	begin
		exec sp_configure 'ole automation procedures', 1;
		reconfigure;
	end
	
end


declare @hr int;
declare @fso int;
declare @driveletter char(1);
declare @odrive int;
declare @totalsize varchar(20);
declare @volumename nvarchar(256);
declare @mb numeric; 
declare @instancename varchar(55);
set @mb = 1048576;
set @driveletter = '';
select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


if object_id('tempdb..#fixeddrives') is not null
	drop table #fixeddrives;

create table #fixeddrives (
	[Drive]	varchar(10) primary key, -- drive letter
	[Drive Label] nvarchar(256), -- drive label
	[Total Capacity MB] numeric(10,2), -- total capacity of the drive in mb
	[Space Used MB] numeric(10,2), -- space used in mb
	[Free Space MB] numeric(10,2), -- free space in mb
	[% Free] numeric(10,2) -- percentage free as of now
);

insert into #fixeddrives([Drive], [Free Space MB]) 
exec master..xp_fixeddrives;

exec @hr=sp_OACreate 'scripting.filesystemobject',@fso out 
if @hr <> 0 exec sp_oageterrorinfo @fso

while exists (select 1 from #fixeddrives d where d.Drive > @driveletter)
begin
	select top 1 @driveletter = d.Drive 
	from #fixeddrives d 
	where d.Drive > @driveletter 
	order by d.Drive asc

	exec @hr = sp_OAMethod @fso,'getdrive', @odrive out, @driveletter; 
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	exec @hr = sp_OAGetProperty @odrive,'totalsize', @totalsize out;
		if @hr <> 0 exec sp_oageterrorinfo @odrive;

	exec @hr = sp_OAGetProperty @odrive,'volumename', @volumename out;
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	update #fixeddrives 
	set [Total Capacity MB]= @totalsize/@mb ,
			[Drive Label]= @volumename
	where
		Drive = @driveletter; 
		
end

exec @hr=sp_OADestroy @fso; 

if @hr <> 0 
exec sp_oageterrorinfo @fso;

update #fixeddrives 
set [Space Used MB]= [Total Capacity MB] - [Free Space MB],
		[% Free] = [Free Space MB]* 100/[Total Capacity MB];


-- revert the option on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'ole automation procedures') <> @oleautomationprocedures
	begin
		exec sp_configure 'ole automation procedures', @oleautomationprocedures;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end
end



-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html>
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> Disk Drives Space and Utilization Information:</div><br></div></div><br>
		<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
		'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
			<th style="border: 1px solid #000000;" align=center>Drive</th>
			<th style="border: 1px solid #000000;" align=center>Drive Label</th>
			<th style="border: 1px solid #000000;" align=center>Total Capacity [MB]</th>
			<th style="border: 1px solid #000000;" align=center>Space Used [MB]</th>
			<th style="border: 1px solid #000000;" align=center>Free Space [MB]</th>
			<th style="border: 1px solid #000000;" align=center>% Free</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + [Drive] + '</td>' +
			'<td style="border: 1px solid #000000;">' + [Drive Label]+ '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar, [Total Capacity MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[Space Used MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[Free Space MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[% Free]) + '</td></tr>'FROM #fixeddrives

		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		
		-- Send email notification
		SELECT @MailSubject = @instancename + ' Disk Drives Space and Utilization Information'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
set nocount off	

Here’s an example of what the report look like via email:
Disk Drives Space and Utilization Informaiton