Disk Space Monitoring

[EdwinGene]

We want to monitor free disk space on the hard drives of our SQL Server boxes, but we want to do it by percentage of free space so that we can reuse the same monitor, regardless of the size of the hard drives on the box, and not have to create one-off monitors for each server.

The xp_fixeddrives extended stored procedure will return the amount of free space on the servers drives in MBs.

Is there a procedure that will return the Percentage of free space on each drive or, barring that, any procedure that will return the total size of each hard drive (or even one specified hard drive) and we can then do the calculation ourselves in a stored procedure?

Thanks.

 

[mikej336]

Do a quick google search on xp_fixeddrives.

I found several options on the first page.

Mike

 

[EdwinGene]

You're talking about "several options" on the Google results page? You obviously saw the numerous links in the Google results page (as I did when I googled xp_fixeddrives, earlier), but you didn't actually follow and read any of them, did you? Because, if you had, you would have realized they don't answer my question. I do actually do my own research before I start asking other people. xp_fixeddrives only returns the actual amount of free space on each of the drives on the box. Nothing that I read in any of the Google links told me that it did anything else. Additionally, none of the links gave any solutions for finding the percentage of free space on the hard drives of the server box.

I also have serveral URLs with lists of undocumented SQL Server stored procedures. I have looked at those pages, also.

Sorry, Mike, but I get testy when someone questions (intentionally or not) my intelligence.

So, my original question (which goes beyond just xp_fixeddrives) still stands: Does anyone know of a procedure that will return the Percentage of free space on each drive or, barring that, any procedure that will return the total size of each hard drive (or even one specified hard drive) or any other way to get the total size of a hard drive, and we can then do the calculation ourselves in a user stored procedure?

 

[Snakeroot]

Here's what my google search turned up:

http://www.lazydba.com/sql/1__16047.html

Create this in master...

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE stp_diskspace 
/*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/ 

AS 
SET NOCOUNT ON 
DECLARE @hr int 
DECLARE @fso int 
DECLARE @drive char(1) 
DECLARE @odrive int 
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576 
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso 
DECLARE dcur CURSOR LOCAL FAST_FORWARD 
FOR SELECT drive from #drives ORDER by drive 
OPEN dcur FETCH NEXT FROM dcur INTO @drive 
WHILE @@FETCH_STATUS=0 
BEGIN 
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive 
End 
Close dcur 
DEALLOCATE dcur 
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
drive, FreeSpace as 'Free(MB)', TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
ORDER BY drive DROP TABLE #drives Return

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[EdwinGene]

Snakeroot, Yes I eventually found this. Unfortunately, we have OLE Automation turned off as part of our security configuration, so I can't use the sp_OA stored procedures.

Thanks, anyway, though.