Part 4 Reality changed this intro.
I was all set with this great intro when reality barged in. We just had an incident today where this script made my day. We monitor server freespace and overnight our space dropped from 19.23g to 12.51g almost 7g on a allocated 30g partition. We looked for the cause and no major dump of files under 100 meg occurred. We checked and all files over 100g looked normal. We rebooted and the missing space reappeared. The only file difference was a cache file that disappeared after reboot but it only had 152 meg, nowhere near the 6+g. Obviously a problem with the 2003 Server. We are not sure what specifically happened. The logs showed no abnormalities. I have seen this with NT servers, but never on a 2003 server. The trouble was detected was not related to the SQL database size but server space, but two more days with drops like these could have shutdown the database.
This time we are going to read the size of the SQL Server size, freespace, used space, get the percentage free, put it all into an Excel spread, part Lake Michigan and make a mocha latte. Okay maybe I kind of lied about parting Lake Michigan and making a mocha latte, but the rest was true - honest.!
What is needed is a means to monitor historically the changes in space on a server. If a server with 20 gig free space is slowly disappearing 100 meg a week, no problem! But what if this changes to 1 gig a week. Time to look and see what is eating the space. Like part 3, we need to run a script that will dump the space data into a Excel spreadsheet and forewarn us of impending doom and gloom. The name of these scripts are "excelSpaceSRV1.vbs" and "excelSpaceSRV2"
The difference is that in excelSpaceSRV1.vbs you can access the server directly. With excelSpaceSRV2 you have to map a drive to the server and drive directly. Notice the "Const wbemFlagReturnImmediately = &h10" and "Const wbemFlagForwardOnly = &h20" are in both files, even though they are used in only one. This is common when you look at VB Scripts. It doesn't hurt, but normally I remove anything not needed. The boilerplate nature of VB Scripting seems to sanctify this especially in multiple purpose scripts where you can use the script for many applications with simple changes in the code. We tend to put a whole bunch of constants,sub routine and functions in the code.. Some of the constants "Const" values are a bear to find values for so we list them all to prevent having to look the up again.
Like the other scripts the first sections of the code sets some constants and variables. The SELECT calls are similar but the first runs against the yourServerName server directly and the second runs on your computer using a where clause against a logical drive that you mapped to yourServerName server.
The next part of the code gets and day info, converts the data into gigabytes (/1073741824) and round the result to 2 decimal places, sets some aliases where used. Then has a listing of "wscript.echos" which show what the script has produced for the excel spreadsheet and is used only for testing and can be commented out.
As in Part 3 the rest of the code creates an excel spreadsheet and populates it with the collected data. I collect Size, Free Space, Used Space and Percentage Used. We have to create reports for different users and everyone seems to want different info in different forms. The graphing part of the reporting is the same, bar, line, pie, 3 dimensional and export to non-linear models. Our use is mainly for alerting, predicting future usage and budgetary values, and so on. You may want to simplify the code by removing some of the reported items. I know of a person that only reports the date and used space. Do what you want.
excelSpaceSRV1.vbs
'font-family:"Courier New"'> ' *************************************************
' * MONITOR SERVER SPACE rec Ver 1.0 08-11-2006 *
' * This is for a specific server and drive *
' * requires rights to server *
' *************************************************
' On Error Resume Next
Const xlDown = -4121
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
DIM strDate,strWeekDay,strComputer,strDrive,strSize,strFree
DIM strUsed,strPct,strQuery,strYe,strMo,strDa
strComputer = " yourServerName "
strQuery = "SELECT * FROM Win32_LogicalDisk WHERE DeviceID = 'C:'"
SET oWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
SET cItems = oWMIService.ExecQuery(strQuery, "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
strYe = DatePart("yyyy", Now())
strYe = CStr(strYe)
strMo = DatePart("m", Now())
strMo = Cstr(strMo)
If LEN(strDa) < 2 Then strMo = "0" & strMo
End If
strDa = DatePart("d", Now())
strDa = CStr(StrDa)
If LEN(strDa) < 2 Then
strDa = "0" & strDa
End If
strDate = strYe & " " & strMo & " " & strDa
strWeekDay = Weekday(Now())
For Each oItem In cItems
If oItem.Size <> "" Then
Select Case strWeekDay
Case "0"
strWeekDay = "Sat"
Case "1"
strWeekDay = "Sun"
Case "2"<
strWeekDay = "Mon"
Case "3"
strWeekDay = "Tue"
Case"4"
strWeekDay = "Wed"
Case "5"
strWeekDay = "Thu"
Case "6"
strWeekDay = "Fri"
End Select
strDrive = oItem.DeviceID
strSize = oItem.Size/1073741824
strSize = Round(strSize, 2)
strFree = oItem.FreeSpace/1073741824
strFree = Round(strFree, 2)
strUsed = (oItem.Size - oItem.FreeSpace)
strUsed = strUsed/1073741824
strUsed = Round(strUsed, 2)
strPct = (oItem.FreeSpace/oItem.Size)*100
strPct = Round(strPct, 2)
End If
' Comment out below wscript.echo after testing
WScript.Echo strDate
WScript.Echo strWeekDay
WScript.Echo strComputer
WScript.Echo strDrive
WScript.Echo strSize
WScript.Echo strFree
WScript.Echo strUsed
WScript.Echo strPct
SET oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
SET oWorkbook = oExcel.Workbooks.Open("c:\ excelSpaceSRV1.xls")
SET oRange = oExcel.Range("A1")
oRange.End(xlDown).Activate
intNewRow = oExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
' Shows where cursor is - comment out after testing WScript.Echo "intNewRow = " & intNewRow
WScript.Echo "strNewCell = " & strNewCell
oExcel.Range(strNewCell).Activate
oExcel.Cells(intNewRow, 1).Font.Size = 14
oExcel.Cells(intNewRow, 1).Borders.LineStyle = True
oExcel.Cells(intNewRow, 1).Value = strDate
oExcel.Cells(intNewRow, 2).Font.Size = 14
oExcel.Cells(intNewRow, 2).Borders.LineStyle= True
oExcel.Cells(intNewRow, 2).Value = strWeekDay
oExcel.Cells(intNewRow, 3).Font.Size = 14
oExcel.Cells(intNewRow, 3).Borders.LineStyle = True
oExcel.Cells(intNewRow, 3).Value = strComputer
oExcel.Cells(intNewRow, 4).Font.Size = 14
oExcel.Cells(intNewRow, 4).Borders.LineStyle = True
oExcel.Cells(intNewRow, 4).Value = strDrive
oExcel.Cells(intNewRow, 5).Font.Size = 14
oExcel.Cells(intNewRow, 5).Borders.LineStyle = True
oExcel.Cells(intNewRow, 5).Value = strSize
oExcel.Cells(intNewRow, 6).Font.Size = 14
oExcel.Cells(intNewRow, 6).Borders.LineStyle = True
oExcel.Cells(intNewRow, 6).Interior.ColorIndex = 6
oExcel.Cells(intNewRow, 6).Value = strFree
oExcel.Cells(intNewRow, 7).Font.Size = 14
oExcel.Cells(intNewRow, 7).Borders.LineStyle = True
oExcel.Cells(intNewRow, 7).Value = strUsed
oExcel.Cells(intNewRow, 8).Font.Size = 14
oExcel.Cells(intNewRow, 8).Borders.LineStyle = True
oExcel.Cells(intNewRow, 8).Value = strPct
oRange.EntireColumn.AutoFit()
'font-family:"Courier New"'>excelSpaceSRV2.vbs
'font-family:"Courier New"'> ' *************************************************
' * MONITOR SERVER SPACE rec Ver 1.0 08-11-2006 *
' * This is for a specific server and drive *
' * requires mapped drive R to server and drive *
' *************************************************
' On Error Resume Next
Const xlDown = -4121
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
DIM strDate, strWeekDay, strComputer, strDrive, strSize, strFree, strUsed, strPct
DIM strQuery, strYe, strMo, strDa, strCompAlias, strDriveAlias
strComputer = "."
Set oWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set cItems = oWMIService.ExecQuery _
("Select * from Win32_LogicalDisk WHERE Name='R:'")
strYe = DatePart("yyyy", Now())
strYe = CStr(strYe)
strMo = DatePart("m", Now())
strMo = Cstr(strMo)
If LEN(strMo) < 2 Then
strMo = "0" & strMo
End If
strDa = DatePart("d", Now())
strDa = CStr(StrDa)
If LEN(strDa) < 2 Then
strDa = "0" & strDa
End If
strDate = strYe & " " & strMo & " " & strDa
strWeekDay = Weekday(Now())
If strComputer = "." Then
strCompAlias = "yourServerName"
End If
For Each oItem In cItems
If oItem.Size <> "" Then
Select Case strWeekDay
Case "0"
strWeekDay = "Sat"
Case "1"
strWeekDay = "Sun"
Case "2"
strWeekDay = "Mon"
Case "3"
strWeekDay = "Tue"
Case "4"
strWeekDay = "Wed"
Case "5"
strWeekDay = "Thu"
Case "6"
strWeekDay = "Fri"
End Select
strDrive = oItem.DeviceID
If strDrive = "R:" Then
strDriveAlias = "C:"
Else
strDriveAlias = "WRONG"
End If
strSize = oItem.Size/1073741824
strSize = Round(strSize, 2)
strFree = oItem.FreeSpace/1073741824
strFree = Round(strFree, 2)
strUsed = (oItem.Size - oItem.FreeSpace)
strUsed = strUsed/1073741824
strUsed = Round(strUsed, 2)
strPct = (oItem.FreeSpace/oItem.Size)*100
strPct = Round(strPct, 2)
End If
Next
' Comment out below wscript.echo after testing
WScript.Echo strDate
WScript.Echo strWeekDay
WScript.Echo strComputer
WScript.Echo strDrive
WScript.Echo strDriveAlias
WScript.Echo strSize
WScript.Echo strFree
WScript.Echo strUsed
WScript.Echo strPct
SET oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
SET oWorkbook = oExcel.Workbooks.Open("c:\ excelSpaceSRV2.xls")
SET oRange = oExcel.Range("A1")
'oRange.End(xlDown).Activate
intNewRow = oExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
WScript.Echo "intNewRow = " & intNewRow
WScript.Echo "strNewCell = " & strNewCell
oExcel.Range(strNewCell).Activate
oExcel.Cells(intNewRow, 1).Font.Size = 14
oExcel.Cells(intNewRow, 1).Borders.LineStyle = True
oExcel.Cells(intNewRow, 1).Value = strDate
oExcel.Cells(intNewRow, 2).Font.Size = 14
oExcel.Cells(intNewRow, 2).Borders.LineStyle = True
oExcel.Cells(intNewRow, 2).Value = strWeekDay
oExcel.Cells(intNewRow, 3).Font.Size = 14
oExcel.Cells(intNewRow, 3).Borders.LineStyle = True 'mso-tab-count:1'>
oExcel.Cells(intNewRow, 3).Value = strCompAlias
oExcel.Cells(intNewRow, 4).Font.Size = 14
oExcel.Cells(intNewRow, 4).Borders.LineStyle = True
oExcel.Cells(intNewRow, 4).Value = strDriveAlias
oExcel.Cells(intNewRow, 5).Font.Size = 14
oExcel.Cells(intNewRow, 5).Borders.LineStyle = True
Excel.Cells(intNewRow, 5).Value = strSize
oExcel.Cells(intNewRow, 6).Font.Size = 14
oExcel.Cells(intNewRow, 6).Borders.LineStyle = True
oExcel.Cells(intNewRow, 6).Interior.ColorIndex = 6
oExcel.Cells(intNewRow, 6).Value = strFree
oExcel.Cells(intNewRow, 7).Font.Size = 14
oExcel.Cells(intNewRow, 7).Borders.LineStyle = True
oExcel.Cells(intNewRow, 7).Value = strUsed
oExcel.Cells(intNewRow, 8).Font.Size = 14
oExcel.Cells(intNewRow, 8).Borders.LineStyle = True
oExcel.Cells(intNewRow, 8).Value = strPct
oRange.EntireColumn.AutoFit()
The Excel for todays crisis is below
The Excel graph for todays crisis is below not hard to tell something is wrong!
WOW! After I ran the script, a mocha latte appeared on my desk. I told you this stuff was magic. Okay, well maybe my cohort picked it up at the local coffee house. Did anyone check the lake?