master..xp_fixeddrives returns the amount of free space on the fixed drives.  I am looking for a command that may return the amount of total space on the fixed drives.   Or maybe even a command that will return the total amount of space if the specific drive is specified...

Any ideas?

Jeff

Well, I've always wondered why MS couldn't just add a darn column with total space, but it was not to be.

The only way, I know of, is to use other methods outside SQL Server, such as a VB or ASP script, that uses the FSO (file system object).

Also, you can use Srvinfo.exe which is in the W2K Resource kit. It does output a lot of info about the server,services, uptime, etc., but also all the disk volumes with total, free, and used space.

You can execute this via xp_cmdshell, and perhaps insert it into a table, and then get just the disk info.  Just an idea that hopefully you can work with.

HTH

You can also get this from VBScript using the FileSystemObject.

you can use SP_OACreate, SP_OAMethod and other SP_OA sps to use ole objects in SPs just like in VB or VBS

Imran

I thought I found this script on this website. Below is a script that can be run against the server to get the fixed drive total space:

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

I hope this helps,

McDBA

The script is awesome!!!