SQLServerPedia

Q: I want to retrieve total space and used space of all drives also i want to retrive the size on disk value for a particular folder which is located inside the drives – how can I do this?

Kevin Kline says: Write a simple Transact-SQL stored procedure that uses XP_CMDSHELL to do DIR statements (or other DOS statements) against the drives and folders you want to know about.

You could use some of the file system functions, such as fn_virtualfilestats. Read about this at http://technet.microsoft.com/en-us/library/ms187309.aspx