Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Monitoring File Sizes in SQL Server Part 4

By Roy Carlson,

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?

Total article views: 6633 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Monitoring File Sizes in SQL Server Part 3

Trending the size data for your databases can be crucial when ordering new hardware or just ensuring...

FORUM

SQL Server Services

.bat file to check the status of SQL Server windows services

FORUM

What are WMI classes for SQL server performance counters?

Hi, I want to use WMI to retrieve SQL server performance counters for my Performance Monitor. Can so...

FORUM

when was sql server stopped?

Is there way to tell when was sql server stopped? I know i can look in the sql server logs but that ...

FORUM

Rename SQL server Server

Rename SQL server Server

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones