PowerSQL – Different Approaches to Find Free Space in all disk drives

Different ways to find FreeSpace  in all disk drives of a given server

  • T-SQL
  • Using WMIObject
  • Counters

All three methods are explained below

This post illustrates the simplest way of doing anything with ease using Powershell. I hope everyone agree with it.

Download the code here Code- DiskSpaceUsageDetails

TSQL

TSQL
/* Enable Ole and XP_CMDShell object*/

—- Allow advanced options to be changed.
sp_configure ‘show advanced options’, 1;
GO
— To update the currently configured value for advanced options.
RECONFIGURE;
GO
— To enable the feature.
sp_configure ‘Ole Automation Procedures’, 1;
Go
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature without restarting of your sql instance
RECONFIGURE;
GO
DECLARE @hr INT ,
@fso INT,
@drive CHAR(1),
@odrive INT,
@TotalSize VARCHAR(20),
@MB NUMERIC ,
@FreeSpace INT,
@free INT,
@RowId_1 INT,
@LoopStatus_1 SMALLINT,
@TotalSpace VARCHAR(10),
@Percentage VARCHAR(3)

——————————————————————————————-
–Table to Store Drive related information
——————————————————————————————-
CREATE TABLE #drives
(
id INT IDENTITY(1,1) PRIMARY KEY,
drive CHAR(1),
FreeSpaceMB INT ,
TotalSizeMB INT NULL,
percentage INT
)

——————————————————————————————-
–Inserting the output of xp_fixeddrives to #SpaceSize Table
——————————————————————————————-
INSERT #drives(drive,FreeSpaceMB) EXEC master.dbo.xp_fixeddrives

——————————————————————————————-
–Using the sp_OACreate, sp_OAMethod and sp_OAGetProperty system stored
–procedures to create Ole Automation (ActiveX) applications that can do
–everything an ASP script can do*/
–Creates an instance of the OLE object
——————————————————————————————-
EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT

SET @MB = 1048576
SET @RowId_1 = 1
SET @LoopStatus_1 = 1

——————————————————————————————-
–To Get Drive total space
——————————————————————————————-

WHILE (@LoopStatus_1 <> 0) BEGIN

SELECT
@drive=drive,
@FreeSpace=FreeSpaceMB
FROM
#drives
WHERE
( ID = @RowId_1 )

IF ( @@ROWCOUNT = 0 )
BEGIN
SET @LoopStatus_1 = 0
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
EXEC @hr =sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT
UPDATE #drives SET TotalSizeMB=@TotalSize/@MB
WHERE
drive=@drive
UPDATE #drives SET Percentage=(@FreeSpace/(TotalSizeMB*1.0))*100.0
WHERE drive=@drive
END
SET @RowId_1 = @RowId_1 + 1

END

SELECT drive,FreeSpaceMB/1024.00 FreeSpaceGB,TotalSizeMB/1024.00 TotalSizeGB,Percentage [FreeSpace %] FROM #drives

DROP TABLE #drives

Output –

DiskSpaceDetails-1

Using Get-WMIObject

$serverName=’AQDBPS8’#Change input Server Name
Get-WmiObject win32_logicalDisk -ComputerName $ServerName |
select DeviceID,VolumeName,
@{Expression={$_.Size /1Gb -as [int]};Label=”Total Size(GB)”},
@{Expression={($_.Size /1Gb -as [int]) – ($_.Freespace / 1Gb -as [int])};Label=”InUse Size (GB)”} ,
@{Expression={$_.Freespace / 1Gb -as [int]};Label=”Free Size (GB)”},
@{Expression={(($_.Freespace /1Gb -as [float]) / ($_.Size / 1Gb -as [float]))*100};Label=”FreeSpace (%)”} |ft -AutoSize

Output :

DiskSpaceDetails-2

Invoke Get-Counters cmdlet

$serverName=’HQDBSP18′
get-counter -computername $serverName -counter “\LogicalDisk(*)\% free space”

DiskSpaceDetails-3

Thanks for reading my space. More to come….

Happy learning!!!

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL, T-SQL and tagged , , , , . Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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