Part 3 . . . The card you were thinking of is the three of clubs!

All right! So I am not a mind reader. But I do have a photographic memory. Unfortunately, I have no film. Because of this uniqueness, I need a mnemonic aid. Excel to the rescue.

With the script in Part 3, we are going to read the size of the SQL instances, put the data in an Excel spreadsheet and produce a chart where the growth can be tracked over time. If you have one or two SQL Servers to monitor this article may not interest you. If your data is unchanging or not very dynamic again your interest my not be here. If you have to watch a large quantity of servers, highly dynamic this simple script may be of help.

We have highly seasonal data, hitting our databases from faxes, online, employees, trading partners, mainframe input, scanners, etc. The volumes are somewhat predictable but if there is a special promotion or industry change the effects can be dramatic. Likewise database maintenance performed on a scheduled basis shows dramatic changes in the size of files. Historical monitoring showed us the need to change the cycles of maintenance or modify them entirely.

The sample showing the Northwind and pubs databases have been altered to protect the innocent and any coincidence between . . . er, forget that.

Excel using the graphing function, shows us the status instantly. We typically run a series of these Excel sheets on startup sometimes displaying multiple graphs. They show us at a glance the need for action and/or the result of an action on the SQL databases. It is a very quick and painless operation to run through the series of these every day, week or month. The interval depends on the dynamics of the particular database. Some change very little, others very often.

How did we know that? Well it is because we ran these Excel collections and discovered that very thing. Amazing! No Hocus Pocus or Mentalist talent required. When we found one of SQL persons who normally manually monitored the databases was found in a state of stupor, mumbling incoherently, we decided to automate the collection. The second revelation when the same person was found dazed and babbling staring at the Excel spreadsheet was when we decided to chart the result. Now we discovered that this person was normally in this state of stupor and constantly mumbled. Non-the-less we discovered it was an easy way to monitor our SQL server database history.

Yes I know we can use Trace and thats a great tool, but there is much less overhead to run the script. Another use we found was to run the script every half hour on one specific application. This server was a transaction nightmare. We were able to track the process throughout the day. Since this particular application required continual access by users, we were able to vary the process input function to enable user access within acceptable time limits without compromising data integrity.

yourExcelDB.vbs

' *************************************************

' * MONITOR DATABASE SIZE rec Ver 1.0 9-18-2006 *

' * This is for a specific server and drive *

' * to run on multiple drive set up array of *

' * strQuery and strComputer variables or an *

' * simple solution is to step and repeat this *

' * code for each server and drive. *

' *************************************************

' On Error Resume Next

Const xlDown = -4121

' just some declared variables

DIM dtmDate,dtmWeekDay,strComputer,strDrive,strSize

DIM strFree,strUsed,strPct,strQuery,dtmYe,dtmMo,dtmDa

' Lists all the files on a computer. Change to your server name.

strComputer = "yourServerName"

Set objWMIService = GetObject("winmgmts:\\" & strComputer)

Set colFiles = objWMIService.ExecQuery _

("SELECT * FROM CIM_Datafile WHERE Drive='E:' AND Path = '\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\' AND FileName = 'Northwind' AND Extension = 'mdf'")

   For Each objFile in colFiles

   strName1 = objFile.FileName & "." & objFile.Extension

   strSize1 = objFile.FileSize

   strSize1 = strSize1/1048576

   strSize1 = Round(strSize1, 2)

Wscript.Echo strName1 & " " & strSize1

Next

Set colFiles = objWMIService.ExecQuery _

("SELECT * FROM CIM_Datafile WHERE Drive='E:' AND Path = '\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\' AND FileName = 'Northwnd' AND Extension = 'ldf'")

   For Each objFile in colFiles

   strName2 = objFile.FileName & "." & objFile.Extension

   strSize2 = objFile.FileSize

   strSize2 = strSize2/1048576

   strSize2 = Round(strSize2, 2)

Wscript.Echo strName2 & " " & strSize2

Next

Set colFiles = objWMIService.ExecQuery _

("SELECT * FROM CIM_Datafile WHERE Drive='E:' AND Path = '\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\' AND FileName = 'pubs' AND Extension = 'mdf'")

   For Each objFile in colFiles

   strName3 = objFile.FileName & "." & objFile.Extension

   strSize3 = objFile.FileSize

   strSize3 = strSize3/1048576

   strSize3 = Round(strSize3, 2)

Wscript.Echo strName3 & " " & strSize3

Next

Set colFiles = objWMIService.ExecQuery _

("SELECT * FROM CIM_Datafile WHERE Drive='E:' AND Path = '\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\' AND FileName = 'pubs_log' AND Extension = 'ldf'")

   For Each objFile in colFiles

   strName4 = objFile.FileName & "." & objFile.Extension

   strSize4 = objFile.FileSize

   strSize4 = strSize4/1048576

   strSize4 = Round(strSize4, 2)

Wscript.Echo strName4& " " & strSize4

Next

dtmYe = DatePart("yyyy", Now())

strYe = CStr(dtmYe)

dtmMo = DatePart("m", Now())

strMo = Cstr(dtmMo)

If LEN(strMo) < 2 Then

strMo = "0" & strMo

End If

dtmDa = DatePart("d", Now())

strDa = CStr(dtmDa)

If LEN(strDa) < 2 Then

strDa = "0" & strDa

End If

strDate = strYe & " " & strMo & " " & strDa

SET oExcel = CreateObject("Excel.Application")

oExcel.Visible = True

SET oWorkbook = oExcel.Workbooks.Open("c:\yourExcelDB.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

      If (intNewRow Mod 2 = 1) Then

      oExcel.Cells(intNewRow, 1).Interior.ColorIndex = 6

      End If

   oExcel.Cells(intNewRow, 1).Value = strDate

   oExcel.Cells(intNewRow, 2).Font.Size = 14

   oExcel.Cells(intNewRow, 2).Borders.LineStyle = True

      If (intNewRow Mod 2 = 1) Then

      oExcel.Cells(intNewRow, 2).Interior.ColorIndex = 6

      End If

   oExcel.Cells(intNewRow, 2).Value = strSize1

   oExcel.Cells(intNewRow, 3).Font.Size = 14

   oExcel.Cells(intNewRow, 3).Borders.LineStyle = True

      If (intNewRow Mod 2 = 1) Then

      oExcel.Cells(intNewRow, 3).Interior.ColorIndex = 6

      End If

   oExcel.Cells(intNewRow, 3).Value = strSize2

   oExcel.Cells(intNewRow, 4).Font.Size = 14

   oExcel.Cells(intNewRow, 4).Borders.LineStyle = True

      If (intNewRow Mod 2 = 1) Then

      oExcel.Cells(intNewRow, 4).Interior.ColorIndex = 6

      End If

   oExcel.Cells(intNewRow, 4).Value = strSize3

   oExcel.Cells(intNewRow, 5).Font.Size = 14

   oExcel.Cells(intNewRow, 5).Borders.LineStyle = True

      If (intNewRow Mod 2 = 1) Then

      oExcel.Cells(intNewRow, 5).Interior.ColorIndex = 6

      End If

   oExcel.Cells(intNewRow, 5).Value = strSize4

oRange.EntireColumn.AutoFit()

BEFORE YOU RUN THE SCRIPT! First create an Excel spreadsheet with the headings based upon the database you wish to monitor, save it as "yourExcelDB.xls" or the name of your choice. If you rename the file or the path then change it at the line:

SET oWorkbook = oExcel.Workbooks.Open("c:\yourExcelDB.xls")

Again save the script, run it from a command prompt ">cscript yourExcelDB.vbs" or from a batch file.

Okayhere is the explanation of the yourExcelDB.vbs script:

The Const xlDown = -4121 is just the control down arrow command to move the cursor down to the next empty space in that column. This may give you trouble, so if the cursor moves to row 66,000 or so, then save the Excel spreadsheet with a number in the A2 area (first column,second row) and rerun. It should now work and you can delete the row with the inserted number. The WScript.Echo strNewCell should be at that new location.

CHANGE the line "strComputer="yourServername"" to match your server.

I am using four versions of a SELECT for each database mdf and ldf file. This is done for two reasons:

• First to make it easier to format the data return into specific rows and columns of the Excel spreadsheet.
• Second it is easier to understand, the SELECT statement is WQL and not SQL and it gets tricky with multiple where calls.

In the SELECT statement CHANGE the Drive, Path, FileName and Extension to the database file you want to get data on. The Drive needs the colon as shown. The Path needs the double slashes - \\. There is no period in the Extension call.

The code extracts the name, extension, and size of the each file. All we really need is the size, but when testing I like to make sure that the correct file is associated with the size. The "Wscript.Echo strName1 & " " & strSize1" returns the filename and its size and can be commented out with a single quote mark when your script goes to production.

The size data is returned in bytes, which is converted to megabytes (by dividing by 1048576) and rounded to 2 decimal points. The conversion and rounding can be alter to meet your needs.

The next step is to create a date code which is not a file date but a date when the data was extracted for the spreadsheet. If you want to simplify the code or need a timestamp replace all the date stuff starting with

dtmYe=DatePart(yyyy,Now()), 12 lines down to strDate=strYe & & strMo & & strDa

with

strDate = Now()

This will return the date and time stamp when the script was run.

In the next seven lines we open the Excel application, make it visible, open the Excel Workbook c:\yourExcelDB.xls (or whatever you have changed it to), move the cursor to the next empty space and make that space active.

The Wscript.Echo intNewRow and strNewCell just tells us in the command window where we are on the spreadsheet. It also can be commented out when you go live.

The five sections starting with oExcel.Cells(intNewRow, Set the Font Size to 14, put a line around the cell, color the cell if the result is odd, and last put the data in starting with the date and ending with the strSize4 which in our sample is the Pubs_log.ldf size.

The Excel charting part is something you will have to do on your own. My charts tend to be simplistic i.e., the data and maybe a trend line. I tend to set the value axis limits to an action required now point. Since I also have other math programs I even import the Excel data into Non-Linear models. But that is an entirely different can of worms.

The fourth magic trick is next.