Hi folks,

I need to find out Drive capacity and free space, I do not have access
to Windows. I just have sa access to the SQL Server. I used
xp_fixeddrives to find out the free space, but how do I know the
capacity of the drive.

Thanks in advance.
-----

*** Sent via Developersdex http://www.developersdex.com ***

Well, if you are using Windows Server 2003, you can use wmic for this.



SET NOCOUNT ON;

CREATE TABLE #drives
(
drive CHAR(2),
MBFree BIGINT
);

CREATE TABLE #scratch
(
lineitem NVARCHAR(2048)
);

INSERT #drives EXEC master..xp_fixeddrives;
-- might be a good idea to call wmic manually once first
-- to make sure it is installed and loaded
INSERT #scratch EXEC master..xp_cmdshell 'wmic volume list'

SELECT d.drive, s.Capacity, d.MBFree,
  [%free] = CONVERT(DECIMAL(5,2), d.MBFree / s.Capacity * 100)
FROM #drives d INNER JOIN (
   SELECT capacity = CONVERT(DECIMAL(10,2), CONVERT(BIGINT,
     SUBSTRING(lineitem, 23, 13)) / 1024 / 1024.0),
       drive = REPLACE(SUBSTRING(lineitem, 112, 2), ':', '')
   FROM #scratch
   WHERE SUBSTRING(lineitem, 125, 1) = '3'
  ) s ON d.drive = s.drive
ORDER BY 1;

DROP TABLE #drives, #scratch;




Else, I would use something outside of SQL Server.  E.g. you can get this
information from scripting.filesystemobject in a VBS script, called through
wscript and scheduled through windows task scheduler, and stuff its output
into the database, instead of expecting the database to do it internally...





Show quoteHide quote

Wow!!! This is what I was looking for. Thanks a lot Aaron. You are
great.

-----

*** Sent via Developersdex http://www.developersdex.com ***

SQL Server vs the Competition
Date Only Data Type
Time Calculation - NEED HELP PLEASE!
Array?
10 seconds for MoveFirst on a rowset?