Mapped Drive - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Mapped Drive

Author

Topic

joe.ingle
Starting Member

14 Posts

Posted - 2003-04-08 : 11:11:31

Does anyone know of, or has anyone written a stored procedure similar to xp_fixeddrives, but which will get the drive letter and size of all mapped drives?

TIA

Joe

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-08 : 11:51:11

Well, I don't know of any DOS commands that will give you the size of a drive....but shouldn't you know that already? Usually I'm worrying about how much is left (which is what the xp does for you).

Any I use the following...maybe it'll help you.

Delete From Ledger_Folder

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\*.*'

Delete From Ledger_Folder_Parsed

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

Brett

8-)

joe.ingle
Starting Member

14 Posts

Posted - 2003-04-08 : 12:22:25

Well no..........I don't know the size of the drive, that's why I want to find out!!

Sorry should have made myself clearer. I need to monitor the drive size of a network drive with a view to sending out an alert when growth gets to x GB. I could of course check using windows explorer on a day to day basis, but have better things to do :-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-08 : 13:51:32

Ok...there are many products that will do that for you...a quick google gave me:

http://www.netlatency.com/spacesentinel.html

Brett

8-)

joe.ingle
Starting Member

14 Posts

Posted - 2003-04-11 : 11:48:02

Hi Brett

Cheers for the link. I decided to take SQL Server out of the loop and knocked up a vbs file, run every couple of hours through WSH.

Works like a treat.

Ta Joe

Private Function FormatBytes(ByVal NumBytes)

ONE_KB = 1024
ONE_MB = ONE_KB * 1024
ONE_GB = ONE_MB * 1024
ONE_TB = ONE_GB * 1024
ONE_PB = ONE_TB * 1024
ONE_EB = ONE_PB * 1024
ONE_ZB = ONE_EB * 1024
ONE_YB = ONE_ZB * 1024

Dim value
Dim txt

If NumBytes <= 999 Then

FormatBytes = NumBytes & " bytes"

ElseIf NumBytes <= ONE_KB * 999 Then

FormatBytes = NumBytes / ONE_KB & " KB"

ElseIf NumBytes <= ONE_MB * 999 Then

FormatBytes = NumBytes / ONE_MB & " MB"

ElseIf NumBytes <= ONE_GB * 999 Then

FormatBytes = NumBytes / ONE_GB & " GB"

ElseIf NumBytes <= ONE_TB * 999 Then

FormatBytes = NumBytes / ONE_TB & " TB"

ElseIf NumBytes <= ONE_PB * 999 Then

FormatBytes = NumBytes / ONE_PB & " PB"

ElseIf NumBytes <= ONE_EB * 999 Then

FormatBytes = NumBytes / ONE_EB & " EB"

ElseIf NumBytes <= ONE_ZB * 999 Then

FormatBytes = NumBytes / ONE_ZB & " ZB"

Else

FormatBytes = NumBytes / ONE_YB & " YB"

End If

End Function

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objFile
Set objFile = objFSO.GetDrive("R")

Dim FreeSpace

FreeSpace = objFile.FreeSpace

If FreeSpace < 524288000 Then

'If FreeSpace < 500 MB

FreeSpace = FormatBytes(FreeSpace)

Set objMail = CreateObject("CDONTS.NewMail")

HTML = " <html>"
HTML = HTML & " <head>"
HTML = HTML & " <title></title>"
HTML = HTML & " <style>P {font-family:Arial;font-size:13px;font-color:#000000;</style>"
HTML = HTML & " "
HTML = HTML & " </head>"
HTML = HTML & " <body bgcolor=""#ffffff"">"
HTML = HTML & " <table width=""100%"" cellpadding=""0"" cellspacing=""0"">"
HTML = HTML & " <tr> "
HTML = HTML & " <td colspan=""3"" bgcolor=""#ffoooo"" height=""1""><p style=""font-size:28px;color:#ffffff;padding-left:4px;padding-top:4px;padding-bottom:4px;padding-right:4px;"">Free drive space on R Drive - <b>"&FreeSpace&"</b></p></td>"
HTML = HTML & " </tr>"
HTML = HTML & " </table>"
HTML = HTML & " </form>"
HTML = HTML & " </body>"
HTML = HTML & " </html>"

objMail.From = """Drive Monitor"" <email@address.co.uk>"
objMail.To = "email@address.co.uk"
objMail.Subject = "!!LOW FREE DISK SPACE ON R DRIVE!!"
objMail.Importance = 2
objMail.Bodyformat = 0
objMail.Mailformat = 0
objMail.Body = HTML
objMail.Send

Set objMail = Nothing

End If

Set objFile = Nothing
Set objFSO = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources