SQL Server Performance Forum – Threads Archive

List free space in a folder

I know master.dbo.xp_fixeddrives lists all free spaces in the drives of a server. But, I would like to know if anyone know how to list free space in a folder of a drive for monitoring purpose? Thanks.

---

How about: xp_cmdshell ‘dir c:yourfolder*.*’ Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

Better to use
Exec Master..xp_cmdshell ‘dir c:yourfolder*.*’
Madhivanan Failing to plan is Planning to fail

---

FWIW, here’s a solution utilizing th sp_OA* procedures.
CREATE FUNCTION dbo.GetDriveSize (@drive CHAR)
RETURNS DECIMAL(38,0)
BEGIN
DECLARE @result INTEGER
DECLARE @objFSO INTEGER
DECLARE @drv INTEGER
DECLARE @cDrive VARCHAR(13)
DECLARE @size VARCHAR(20) SET @cDrive = ‘GetDrive("’ + @drive + ‘")’
EXEC @result = sp_OACreate ‘Scripting.FileSystemObject’, @objFSO OUTPUT IF @result = 0
EXEC @result = sp_OAMethod @objFSO, @cDrive, @drv OUTPUT
IF @result = 0
EXEC @result = sp_OAGetProperty @drv,’FreeSpace’, @size OUTPUT
IF @result<> 0
SET @size = NULL EXEC sp_OADestroy @drv
EXEC sp_OADestroy @objFSO RETURN @size
END
GO
SELECT dbo.GetDriveSize(‘C’)
DROP FUNCTION dbo.GetDriveSize
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Good article on Disk space monitoring
http://www.databasejournal.com/features/mssql/article.php/3080501 —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

---

Thanks for all your input. Our Sys Admin created the following volumes/partitions: G: as a mount point – 1 GB
GATA01 – 15 GB
G:LOG01 – 3 GB I could see used and free spaces when I looked in the properties of GATA01 and G:LOG01. But it only returned free size (1,005,906,844) in the G: drive when I ran sp_OA* procedures provided by Frank or xp_fixeddrives. It seems that only G: is recognized but not the other two volumes even if I specified GATA01 or G:LOG01. Thanks.

---

When I run xp_fixeddrives on one server I see drive MB free
—– ———–
C 27103
E 52932 (2 row(s) affected) And this is also what my function reports. Am I reading this right, that the other partition don’t appear when you execute xp_fixeddrives?
Does the account SQL Server is running under have the permission to see the partitions? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

This is the returned result when exec xp_fixeddrives: C8915
D25730
E76680
F15140
G959
H866 GATA01 and G:LOG01 don’t appear. As a matter of fact GATA01 and G:LOG01 are separate disks under G: mount lun. Both Windows and SQL Server account are the same – all in AD domain with sysadmin privileges.

---

Can you please tell me which are the other two drives mounted as GATA01 and G:LOG01 .
*)Thease two should be some drives in your server.Let me knwo wht are they ?
*)Is it somthing other than C,D,E,F,G,H ? -Rajiv

---

The script provided only uses the GetDrive method of FileSystemObject. The script needs modification to use the Folder method instead. It will take some work to re-write the sample into a Folder listing. I would reccomend that you visit the MS Scripting Centerhttp://www.microsoft.com/technet/scriptcenter/guide/sas_pt1_overview.mspx and then the storage script repositoryhttp://www.microsoft.com/technet/scriptcenter/scripts/storage/default.mspx Converting vbscript into sp_OA procedures is pretty straightforward…

---

Nice links. Thank you! [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

---

I am having same issue. With SQL 2005 clustering 26 character drive limitation is lifted using Mount points. Mount points are used as folder under one drive and silktaji case it is G: drive.
When you use all the fixed drives and other extended stored procedure it will show only the G: drive space information and not the mount point.
how can we find mount points free space information from SQL 2005 cluster setup?
I have searched and googled on the net and so far no luck specific to SQL 2005 mount points exetended procedures or sys procs or functions to find information for mount points in cluster configuration.

---

in SQL 2005 look at Tara’s blog – http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx using clr procedures.

---

]]>