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...
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.
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
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_diskspaceASSET NOCOUNT ONDECLARE @hr intDECLARE @fso intDECLARE @drive char(1)DECLARE @odrive intDECLARE @TotalSize varchar(20)DECLARE @MB bigint ; SET @MB = 1048576CREATE 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_fixeddrivesEXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUTIF @hr <> 0 EXEC sp_OAGetErrorInfo @fsoDECLARE dcur CURSOR LOCAL FAST_FORWARDFOR SELECT drive from #drivesORDER by driveOPEN dcurFETCH NEXT FROM dcur INTO @driveWHILE @@FETCH_STATUS=0BEGINEXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @driveIF @hr <> 0 EXEC sp_OAGetErrorInfo @fsoEXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUTIF @hr <> 0 EXEC sp_OAGetErrorInfo @odriveUPDATE #drivesSET TotalSize=@TotalSize/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE())WHERE drive=@driveFETCH NEXT FROM dcur INTO @driveENDCLOSE dcurDEALLOCATE dcurEXEC @hr=sp_OADestroy @fsoIF @hr <> 0 EXEC sp_OAGetErrorInfo @fsoSELECT ServerName,drive,TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',FreespaceTimestampFROM #drivesORDER BY driveDROP TABLE #drivesRETURNGO
I hope this helps,
The script is awesome!!!