Check Exchange DB size 3

[MoobyCow]

Because I know it's happened to a lot of people and because my DB size is getting close to the 16GB limit, I wrote a script to check the size and send a status email to my account every night (Task Scheduler).

I thought someone else might be able to use it so I'm posting it here. Just copy the text below and save it as a .vbs (putting in your correct info in email and path)


strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_Datafile Where Path = '\\stuff\\' " & _
"AND Drive = 'e:'")

Set objEmail = CreateObject("CDO.Message")
dtmThisDay = Day(Date)
dtmThisMonth = Month(Date)
dtmThisYear = Year(Date)

strBackupName = dtmThisYear & "-" & dtmThisMonth & "-" & dtmThisDay
txtbdy = "Exhange Data Store:" & vbCRLF



For Each objFile in colFiles

txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & objFile.FileSize & vbCRLF & vbCRLF

Next

objEmail.From = "your.email@your.domain"
objEmail.To = "your.email@your.domain"
objEmail.Subject = "Exchange Store Size: " & strBackupName
objEmail.Textbody = txtbdy
objEmail.Send

 

[MoobyCow]

Oh, put the path to your .edb files (\\Program Files\\Exchsrvr\\Mdbdata\\) where it says "\\stuff\\". S

 

[Zelandakh]

and change the drive letter thereafter.

Tried it and got an error but I can see your logic in the VB however that is going to give you a list of files in the folder. What about log files and so on? What about adding up the priv.edb and stm and excluding the public store and then converting the bytes into GB?

 

[MoobyCow]

All I need is a list of files in the folder, if I know the file size of the public EDB files I know if I'm close to the 16 GB. I suppose I could do the math in the script, but I don't see the point.

I don't need to know how many log files I have and how much space they're taking up, I have plenty of space and do full backups of Exchange nightly so they get cleared out, but the logic would be the same.

I'm curious as to what errors you got though. If you don't run it on Exchange the email part will fail, but otherwise nothing should give an error.

 

[Zelandakh]

Given that shortly after your backup finishes you'll be making new logs, you would verbosely specify the priv.edb and the pub.edb and divide by 1,073,741,824 to give the GB figure.

Otherwise it would be easier to use your local unit to START run \\exchangesrvr\e$\program files\exchsrvr\mdbdata which will list the files in GB.

FYI I get an error on line 19 when run on my Exchange Server pointing at the right path on the right drive.

 

[MoobyCow]

I just put it together for myself and thought maybe someone else could get some use out of it so I posted it here. If it doesn't do exactly what you want, add the functionality you need. It's set up like it is so that I can schedule the .vbs file and have it email me every morning so I don't forget to check.

I would guess that something got messed up in the formating of the select line when you copy/pasted so that you're getting a malformed select statement. Otherwise a bad path just gives an empty result.

The select statement is all one line or use _ to move stuff to the next line.

Set colFiles = objWMIService.ExecQuery ("Select * from CIM_Datafile Where Path = '\\stuff\\' " & "AND Drive = 'e:'")

 

[jplannett]

Thank you for this post. This has put alot of peace of mind on me and my IT manager as we try to keep an eye on this.
I have the script working, but when I receive the email it does not have the store size but the size of all the other files.

File name: priv1.edb
File size:

File name: priv1.stm
File size: 578822142

File name: pub1.edb
File size: 828579840

File name: pub1.stm
File size: 367009792

Windows 2000 SP4
Exchange 2000 SP3 Standard Edition
The size of my Priv1.edb is 14.8 GB (15,988,891,648 bytes)

Anyone have any idea why the file size might not show in the script.
As an alternative, I can use bmail (beyond logic) to send me the size, but I would rather have the script working.

 

[MoobyCow]

Hmm, I'm not sure why that would happen, it's a straight forward listing of the files in the directory.

I've actually updated the script since this post so you can try the newer version and see if it fixes the problem.

This version actually puts in a few of the suggestions from Zelandakh as far as formatting. It also selects an event from the logs to let you know how much free space you have in the store so you know whether an offlien defrag would help or not.



Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")

DateToCheck = Date
Enddate = Date + 1
dtmEndDate.SetVarDate Enddate, True
dtmStartDate.SetVarDate DateToCheck, True

Set objEmail = CreateObject("CDO.Message")
dtmThisDay = Day(Date)
dtmThisMonth = Month(Date)
dtmThisYear = Year(Date)

strBackupName = dtmThisYear & "-" & dtmThisMonth & "-" & dtmThisDay
txtbdy = "Exhange Data Store:" & vbCRLF



strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_Datafile Where Path = '\\Program Files\\Exchsrvr\\Mdbdata\\' " & _
"AND Drive = 'f:'")



For Each objFile in colFiles


gbsize = objfile.filesize/1073741824
txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & gbsize & vbCRLF & vbCRLF

Next

Set colEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where TimeWritten >= '" _
& dtmStartDate & "' and TimeWritten < '" & dtmEndDate & "' and EventCode = '1221' and Logfile = 'Application'")

For each objEvent in colEvents

txtbdy = txtbdy & objEvent.Message

Next


objEmail.From = "email@yourdomain.com"
objEmail.To = "email@yourdomain.com"
objEmail.Subject = "Exchange Store Size: " & strBackupName
objEmail.Textbody = txtbdy
objEmail.Send

 

[Zelandakh]

Mooby - now it works correctly. VB isn't my area at all, how do I just pick up the .EDB and .STM files only? I don't care about the logs you see.

Have a star on me and perhaps you could make this into an FAQ in this forum so that it doesn't get lost in a pile of threads?

Thanks.

 

[MoobyCow]

Thanks,

Just cut out this section and you won't see the info about the logs:


Set colEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where TimeWritten >= '" _
& dtmStartDate & "' and TimeWritten < '" & dtmEndDate & "' and EventCode = '1221' and Logfile = 'Application'")

For each objEvent in colEvents

txtbdy = txtbdy & objEvent.Message

Next

 

[Zelandakh]

Sorry, Mooby, that info is very handy, I want to lose the "File name: e000000.chk" and the "File name: e00000.log" lines at the top.

 

[MoobyCow]

Ahh, I see.

I don't have those files in my directory so I didn't worry about checking the extension. I'll get back to you on that tomorrow, right now it's time to do my taxes.

 

[jplannett]

Is this script only for 2003.
I tried to run it on my exch box with windows 2000 sp4 and I get:
E:\Ex_DB_size.vbs(1, 1) Microsoft VBScript runtime error: ActiveX component cant create object: 'WbemScripting.SWbemDateTime'

I know wbem is windows based enterprise management and I do have a c:\winnt\system32\wbem\winmgmt.exe so I don't know where to go.
I tried it on a 2003 box and it appeared to work. I forgot to change the email address and my time was limited, but there was no error.
any ideas?
Sorry to be the problem child.

plannett

 

[MoobyCow]

jplannett.

'WbemScripting.SWbemDateTime' is a 2003 object. Somewhere around here I have an old script with a time routine for 200, I'll see if I can dig it up.

 

[MoobyCow]

Zelendakh,

To only check the file extensions you want you can insert this code into your script:


If objFile.Extension = "edb" or objfile.Extension = "stm" Then

gbsize = objfile.filesize/1073741824
txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & gbsize & vbCRLF & vbCRLF
end if

In place of:

gbsize = objfile.filesize/1073741824
txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & gbsize & vbCRLF & vbCRLF

 

[MoobyCow]

OK,here's the final script (I think).

The date routine should run on 2000 or 2003, and it only checks for .edb and .stm files (change paths and email addresses where needed):

Set objEmail = CreateObject("CDO.Message")

dtmThisDay = Day(Date)
dtmThisMonth = Month(Date)
dtmThisYear = Year(Date)
dtmThisHour = hour(time)

if dtmthismonth < 10 then
dtmthismonth = "0" & dtmthismonth
end if

if dtmthisday < 10 then
dtmthisday = "0" & dtmthisday
end if

dtmbigdate = dtmThisyear & dtmThisMonth & dtmThisday & "000000.000000-300"

dtmTomDay = Day(Date + 1)
dtmTomMonth = Month(Date + 1)
dtmTomYear = Year(Date + 1)
dtmTomHour = hour(time + 1)

if dtmTommonth < 10 then
dtmTommonth = "0" & dtmTommonth
end if

if dtmTomday < 10 then
dtmTomday = "0" & dtmTomday
end if

dtmtomdate = dtmTomyear & dtmTomMonth & dtmTomday & "000000.000000-300"

strBackupName = dtmThisYear & "-" & dtmThisMonth & "-" & dtmThisDay
txtbdy = "Exhange Data Store:" & vbCRLF



strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_Datafile Where Path = '\\Program Files\\Exchsrvr\\Mdbdata\\' " & _
"AND Drive = 'f:'")



For Each objFile in colFiles

If objFile.Extension = "edb" or objfile.Extension = "stm" Then

gbsize = objfile.filesize/1073741824
txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & gbsize & vbCRLF & vbCRLF
end if

Next

Set colEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where TimeWritten >= '" _
& dtmbigdate & "' and TimeWritten < '" & dtmtomdate & "' and EventCode = '1221' and Logfile = 'Application'")

For each objEvent in colEvents

txtbdy = txtbdy & objEvent.Message

Next


objEmail.From = "email@domain.com"
objEmail.To = "email.@domain.com"
objEmail.Subject = "Exchange Store Size: " & strBackupName
objEmail.Textbody = txtbdy
objEmail.Send

 

[jplannett]

I now get the email with all of the file sizes. The reason the file size for the priv1.edb may have been due to McAfee GroupShield 5.2. Since then I have upgraded to GroupShield 6.0 and it now works.
My question is on the file size and how is it calculated.
My priv1.edb is 12.1 GB, but the script returns it as:
Exhange Data Store:
File name: priv1.edb
File size: 167124989
I am aiming for the GB figure or if necessary the MB or KB figure and I can go from there.

plannett

 

[MoobyCow]

The newer version of the script (last version posted) should report the file sizes in GB. File size is reported as bit by the system as a default.

This section does the math:

If objFile.Extension = "edb" or objfile.Extension = "stm" Then

gbsize = objfile.filesize/1073741824
txtbdy = txtbdy & "File name: " & objFile.FileName & "." & objFile.Extension & vbCRLF
txtbdy = txtbdy & "File size: " & gbsize & vbCRLF & vbCRLF
end if

If you're having a problem you can insert:

wscript.echo gbsize

and you will get a popup showing you what the value is being reported as after the calculation.

 

[jplannett]

First and foremost this question is beyond the scope and the script works, so if you would rather let this go, please do. My intention is not to waste your time.
BUT
Just out of curiousity, does this make any sense.
I used the chart from

http://www.t1shopper.com/tools/calculate

the numbers I get back from the script without dividing by 1073741824 are: (not sure why the bottom one is repeated)
167124989
931143679
794894336
174071808
174071808
I noticed they are all 9 digits long. Seem like the priv1 number should be longer. If the system reports it in bits then the first number my priv1 should be 1 digit longer. Looking at the chart from the link above,
8,589,934,592 from chart
8589934592 without commas
167124989 my reported number, which is only 9 digits long.
when you said bits I tried the calculation, a few different ways. I don't know that this is right, but in the end, it seems close to working out.
I added a zero to my priv number *10 (for the zero) then *8 for the bits/byte calculation
167124989*10*8 / 1073741824 = 12.451782
My priv1.edb is 12.1 GB.
If use gbsize = objfile.filesize*80/1073741824 then I get 12.45.

Three lines above I added a 0 so it equals the length of the GB number from the chart. It may not be 100% accurate but it serves the purpose. I multiplied my priv1 number the script gave me by 8 to calculate from bits to bytes. I am guessing the difference from 12.1 GB to 12.45 is lost somewhere with the 8 in the calculation. Probably because I am either not calculating it correctly, or the whole theory is off anyway.

This of course throws off all my other numbers because they appear to be the amount of digits they should be.

This was on windows 2000 sp4 and exchange 2000 sp3.

Thanks for your help and thank you more for the script.

plannett

 

[Zelandakh]

Mooby, I'm writing an FAQ on mail store sizes at the moment. Would you mind if I included this script in there (I'll be quoting you as the author rather than plagiarising).

Thanks.