Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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!!!


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...