August 12, 2009 at 4:47 am
Hello guys,
The Stored procedure "xp_fixeddrives" will give you the free space on the disk. Is there any other sp which gives us the total space on the disk. Expecting a qucik help from you guys.. thanks in advance.
EG:
exec xp_fixeddrives will give you the output
drive MB free
C 4616
D 999
I need the output as
drive Total space
c 5000
d 2000
August 12, 2009 at 8:23 am
I found this:
http://blogs.mssqltips.com/forums/p/916/1969.aspx
Be careful with that extended stored procedure if you have mount points it won't show the right number..
Why don't you explain what you are trying to accomplish?
CEWII
August 12, 2009 at 9:02 pm
hi,
Thanks for the help, i tried using sp_OAGetProperty. But i cannot use this because as part of the security issue this command is blocked by the administrator.
I am building an asp application which should monitor and tell me the available total space and free space on the disk.
Since the application runs on the client machine and the hard disk space of the server should be known i thought of using the built in sp from the database which would satisfy my need. But the sp mentioned above will only give me free space but not the total space available on the disk.
Is there any command shell script which can be run on the sql side in order to achieve this.
I am trying to use sql because it is more flexible and due to security issues which other method has.
December 17, 2009 at 1:37 pm
Keep in mind, if your SAN Admin set up mount points on a drive. The xp_fixeddrives stored proc will not always give true values. I had a Cluster SQL server that was created by another department, and when I had to run a Audit on that server. I ran the xp_fixeddrives command on that server, it reported that it only had 500 MB free. I had to contact the SAN admin to find out how he set up the server, he then indicated that he created mount points on the Drive. He gave me the mount points names, and I was able to run the xp_cmdshell ‘ Dir’ command against the folder/mount point names, to see that server did have allot of space free. So DBA, Talk with your SAN Admin to get all the mount point information.:-D
December 17, 2009 at 1:43 pm
If you can build CLR stored procedures (more secure and flexible than the sp_OA... procs), this page has one that's worked well for me for drive space:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2009 at 1:24 am
To run xp_fixeddrives , you have require system admin rights.
vinayak_mohan (8/12/2009)
Hello guys,The Stored procedure "xp_fixeddrives" will give you the free space on the disk. Is there any other sp which gives us the total space on the disk. Expecting a qucik help from you guys.. thanks in advance.
EG:
exec xp_fixeddrives will give you the output
drive MB free
C 4616
D 999
I need the output as
drive Total space
c 5000
d 2000
December 29, 2009 at 4:25 am
hi,
I think there are no any system views to get total disk space.
but using fsutil and XP_CMDSHELL you can as describe in below link
December 29, 2009 at 6:47 am
late i have found another
EXEC master.dbo.xp_cmdshell 'wmic LOGICALDISK LIST BRIEF'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply