Getting size of disk

Posted on 2006-05-08
Last Modified: 2008-02-07
I found the folllowing:
EXEC master..xp_fixeddrives

This returns me C and E.
But our database is mapped to
   M:\MSSQL$SQL1\Data\ for DATA   mdf
   O:\MSSQL$SQL1\Data\ for LOG      ldf

Is there a way to get the size of M and O
through SQL script.

Question by:TRACEYMARY
    LVL 15

    Assisted Solution

    xp_fixeddrives will only moitor the physical drives on the server.  if M and O are mapped drives the this procedure will not do what you want.
    LVL 7

    Author Comment

    Is there a way to get through sql code the mapped disk size?
    LVL 68

    Assisted Solution

    Note, too, that xp_fixedDrives is only showing you the available space, not the total space.
    LVL 5

    Assisted Solution

    I found a similar question asked on experts-exchange which may help:

    LVL 15

    Expert Comment

    Not that I know of easily.

    You could write a stored procedure and use sp_OACreate and sp_OAMethod with a Scripting.FileSystemObject


    Write a little program to get the information for you.
    LVL 7

    Author Comment

    I have to do some digging around.....i take alook at the sp_OACreate aswell.
    LVL 30

    Accepted Solution

    perhaps this will help:

    EXEC master.dbo.xp_cmdshell 'DIR C: /-C'

    this will return a list of files on C: but the last two lines (excluding the null line)

    show you

    bytes used
    bytes free

    so you could run the output into a table and clean it up something like this:

    CREATE TABLE HoldOutput (Result VARCHAR(8000), Line INT IDENTITY(1,1))

    INSERT INTO HoldOutput
    EXEC master.dbo.xp_cmdshell 'DIR C:\ /-C'

    DELETE HoldOutput WHERE Result IS NULL
    DELETE HoldOutput WHERE Line <= (SELECT MAX(Line) - 2 FROM HoldOutput)

    This will give you the lines with the space used etc. which you can clean up further to get your result.

    Now that I have explained that, I think the sp_OCreate method would be more reliable!!!
    LVL 7

    Author Comment

    But its not the C or E i want the space on M and O which are SAN storage disks..
    I could just write an shell program wsh perhaps and then call i call that in SQL .....
    I got the predicted usage space if all my data files had used all their space .....i now going one step further see if the disks are capable of the predicted space storage........

    LVL 30

    Expert Comment

    Well change it to

    EXEC master.dbo.xp_cmdshell 'DIR M:\ /-C'

    to get stats for M drive rather than C drive.

    FYI if you write a shell program, you can convert it to T-SQL by effectively replacing CreateObject with sp_OCreate etc. but its pretty cumbersome.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now