Monitoring File Sizes in SQL Server Part 4

,

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?

Rate

Share

Share

Rate