[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting size of disk

Posted on 2006-05-08
9
Medium Priority
?
273 Views
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.

Thanks
0
Comment
Question by:TRACEYMARY
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 15

Assisted Solution

by:DonKronos
DonKronos earned 400 total points
ID: 16633944
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.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16633950
Is there a way to get through sql code the mapped disk size?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 16634013
Note, too, that xp_fixedDrives is only showing you the available space, not the total space.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Assisted Solution

by:bwdowhan
bwdowhan earned 600 total points
ID: 16634065
I found a similar question asked on experts-exchange which may help:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21191351.html

0
 
LVL 15

Expert Comment

by:DonKronos
ID: 16634320
Not that I know of easily.

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

or

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

Author Comment

by:TRACEYMARY
ID: 16634659
I have to do some digging around.....i take alook at the sp_OACreate aswell.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 600 total points
ID: 16635618
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!!!
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16638150
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 .....to see if the disks are capable of the predicted space storage........

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16646872
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.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

868 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