July 29, 2004 at 12:02 pm
I would like to create some sort of alert when the free space run under, let's say, 10%. For these I need to know free space and total capacity of each drive on the computer. To discover how much free space is on each of the server's drives I can use xp_fixeddrives. But this is showing only drive letter and free amount in MB.
How can I retrive the capacity of the drive, so that I can calculate the percent of free space from the total?
Help appreciated.
Thanks.
July 29, 2004 at 12:12 pm
Run this stored proc and you will get the desired results.. Its a script I pulled off the net..
====
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME 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, ServerName = host_name(), FreespaceTimestamp = (GETDATE())
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 ServerName,
drive,
TotalSize as 'Total(MB)',
FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
GO
=====
wize
July 29, 2004 at 1:30 pm
Thank yoy, Wise Guy. This helps a lot!
July 29, 2004 at 2:32 pm
That's what I'd do, use the FSO. There are other ways, but this is the easiest and smoothest I've found.
July 29, 2004 at 2:51 pm
Very useful script, but there is one thing that should be changed. As it is you are using the Host_Name() function to populate the ServerName column. Host_Name() returns the name of the workstation, not the server. Just replace it with @@ServerName and you are in business.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 19, 2008 at 3:30 pm
Almost!
Sometimes sys.servers does not have a record for @@ServerName to return, so it comes back null.
The foolproof way is:
select @server_name = cast( SERVERPROPERTY( 'servername' ) as sysname )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply