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 -
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 :
Invoke Get-Counters cmdlet
$serverName=’HQDBSP18′
get-counter -computername $serverName -counter “\LogicalDisk(*)\% free space”
Thanks for reading my space. More to come….
Happy learning!!!
