January 24, 2007 at 2:40 am
Hi Guys,
Does anyone perhaps a script that will give the total drive sizes of a server. For eg. we are using xp_fixeddrives to check the available space left, but we need a script that will give the total size(used and free space). We want to convert the script in a stored proc so that when there is only 10% left on one of the drives on the server, it will send an alert indicating to you how much space is left.
Can anyone pls help?
Regards;
January 24, 2007 at 3:25 am
Hi Imke,
I dont think you can do this directly from TSQL unless you want to use sp_OACreate to create a Scripting.FileSystemObject then use the GetDrive method of that (however OLE stuff is normally turned off in SQL Server 2005 but you can enable it via the Surface Configuration tool).
A better way may be a vbs script which populates a table once every so often with the total size of each drive and then to use the data in that table. Other options if you are happy writing code would be your own extended stored procedure or using a .NET package in SQL Server 2005.
- James
--
James Moore
Red Gate Software Ltd
January 24, 2007 at 7:36 am
You can use the free PSINFO utility from http://www.sysinternals.com. It gives you the numbers you wan directly, including the % of free space. You can run it from XP_CMDSHELL. Here is the command.
psinfo -d
January 25, 2007 at 12:30 am
Hi dclark
Will the command look like this: exec xp_cmdshell 'psinfo -d'?
I'm executing this command but I'm not getting the results that I need.
Am I doing this right?
Regards
January 25, 2007 at 2:41 am
Try this procedure, best to run on master database...
CREATE PROCEDURE usp_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 (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
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
January 25, 2007 at 3:29 am
thank you very much Patrick
January 26, 2007 at 2:03 pm
This is so cool!
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply