Part 1 . . . I'll get you my pretty and your little SQL too.
I understand how the Wicked Witch of the West felt when the house fell on her sister. One of my friends had the house fall on him when their server froze because of a space issue. He dutifully monitored the databases as required in the OZ SQL manual. But the server froze not because of a problem in the database but in a portion of the server which held web data. Temporary data filled the server waiting to be offloaded to another application. It occurred - they think - over a two week period.
We check periodically to make sure there is enough server space and that mdf/ldf sizes are within expected growth level. Is this enough? The problem is that you probably are doing this manually. Maybe you have a whole bunch of servers and may or may not remember "size-to-freespace" relationships correctly. How can you tell whether a gig increase in an mdf file over a week is unique or part of the normal growth? Your user might be insisting on you expanding his space on the SAN because they feel you are going to run out of space for their data and you can't argue because you don't have any data to back you up.
Follow me down the yellow brick road, I can't offer you an appointment with the Wizard, but I can provide several different tools that might give you the data you need to become proactive rather than reactive.
- First Part monitoring the SQL Server mdb and ldb file size with a threshold to produce a visual alert every time you boot you computer.
- Second Part monitoring the SQL Folder size with a threshold to produce a visual alert every time you boot you computer.
- Third Part the export of the mdb and ldb file size to excel
- Fourth Part extracting the size information on your SQL server drives and plotting it to a excel spreadsheet.
All these solutions use vb script. Do not let this script scare you. Most of it is boilerplate I will show you where you have to change it for your needs. It is saved as a vbs file and run from a command prompt, i.e. cscript yourScript.vbs or inserted into a batch file and run automatically. Using Microsoft Scheduler we run the batch files to run on boot up or during quiet times late at night.
TIP: before you run the script type cscript /? at the command prompt. This will tell you if cscript is available and the version number (5.6 needed). Type cscript //H:Cscript. This will make cscript the default so that to run a vbscript all you need to type is the yourScript.vbs at the prompt without the cscript preface. Plus you won't have annoying popup windows requiring you to check ok or whatever. |
The above screen print show the script run against the Northwind and Pubs database files (mdf and ldf). Oh my goodness, Lions and Tigers, Oh My, the Northwind has exceeded 2 megs and the pubs_log.ldf has vanished! What have I done!
Note: both the mdf and ldf files are loaded because you want to know about the size of both. We had a user that only backed up the mdf and the ldf grew to Bigfoot status. Check out BOL or MS for reasons to clear the transactions. |
Below is "yourScript.vbs"
'*** Conversion codes Bites to Megabytes
'and 2 megabytes threshold ***
const CONVERSION_FACTOR = 1048576
const WARNING_THRESHOLD = 2
'Just the date and time
dtmDate = CStr(Now())
'basic object info the dot computer is the computer you are
'running this from and the wmi stuff is setting the objects
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer)
Set colProcesses = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process")
Set objFSO = CreateObject("Scripting.FileSystemObject")
' This creates a file on your computer on the c drive root
' You can send this file wherever you want
Set objNewFile = objFSO.CreateTextFile("c:\Discovery.htm")
' starts writing to the htm file
objNewFile.WriteLine "<HTML><HEAD>"
objNewFile.WriteLine "<TITLE>STATUS REPORT</TITLE></HEAD>"
objNewFile.WriteLine "<BODY BGCOLOR='IVORY'><CENTER><H3>STATUS REPORT - - DATE: " & dtmDate & "</H3>"
' *** the strComputer has to be changed to the SQL server of your choice ***
' *** you must have rights to access "yourSQLserver"
strComputer = "yourSQLserver"
' *******************************************************
' *** Get the file info on the SQL server. ***
' *** change the FileName="yourDatabaseInstance" ***
' *** If you have multiple databases follow the "OR" ***
' *** example below. If the path to your mdf and ldf ***
' *** files are different, change it to match. The ***
' *** The double backslashes are required. Change the ***
' *** Drive to the location of the SQL mdf/ldf files. ***
' *******************************************************
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='Northwnd' OR FileName='pubs' OR FileName='pubs_log'")
objNewFile.WriteLine "<H3>My Database</H3>"
objNewFile.WriteLine "<TABLE BORDERCOLOR='BLACK' BORDER='3' CELLPADDING='2'>"
For Each objFile in colFiles
' below for testing
' WScript.Echo objFile.FileName & "." & objFile.Extension & " " & objFile.FileSize
' writes the collected array to htm file and closes the htm
objNewFile.WriteLine "<TR><TD>" & objFile.FileName & "." & objFile.Extension & "</TD>"
strFileMB = objFile.FileSize / CONVERSION_FACTOR
If strFileMB > WARNING_THRESHOLD Then
objNewFile.WriteLine "<TD BGCOLOR=WHITE><FONT COLOR=RED SIZE=+1><B>CRITICAL</B></FONT></TD>"
objNewFile.WriteLine "<TD BGCOLOR=WHITE>Size " & Int(strFileMB) & " Megabytes.</TD></TR>"
Else
objNewFile.WriteLine "<TD BGCOLOR=GREEN><FONT COLOR=WHITE>Size OK</FONT></TD>"
objNewFile.WriteLine "<TD BGCOLOR=CCFFCC>Size " & Int(strFileMB) & " Megabytes.</TD></TR>"
End If
Next
objNewFile.WriteLine "</TABLE><BR>"
objNewFile.WriteLine "</CENTER></BODY></HTML>"
' *** IE CODE - opens Discovery.htm file with the browser ***
const MAXIMIZE_WINDOW = 3
Set objNetwork = Wscript.CreateObject("Wscript.Network")
Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run "c:\Discovery.htm", MAXIMIZE_WINDOW
Lets gently explain the code. The threshold setting and conversion factor are based upon megabytes and should be changed to meet your needs. The file sizes are reported in bytes. The byte to kilobyte, megabyte, gigabyte and terabyte conversion numbers can be found online. Buried in the code is a conversion of the converted size to an integer "int(strFileMB)" and you can remove the "int" if you want the decimals. Since we have very large files it works very well for us, but you my want to change it. Remember that the "int" function rounds to the nearest integer which is why our pubs_log file is 0. You were worried it vanished. It is just smaller than 1 megabyte. Whew!
The script runs locally. The first part of the script creates the "Discovery.htm" file. We want the Discovery.htm file on our computer. The htm file is overwritten every time the script is run. If you need to archive these reports - off load , rename, or datecode the name of the htm file (see final foot note.). Next the initial part of the htm file is created with a title and date.
Next you have to change the strComputer='mySQLserver' to the name of your SQL Server that you want to monitor. You will need rights to access this server.
The "SELECT" state is NOT SQL or T-SQL. It is WQL. Don't blame me but that is what the Munchkins use in VB ScriptLand and OZ. It is not as powerful as SQL. Be careful. If you try something like "FileName LIKE Nor%'" on a MS 2000 box it will not work. It should work with MS XP.
You will have to change FileName='yourDatabaseInstance' to the database(s) on your server. If you have multiple databases on the same server you will have to enter all that you wish to monitor. The transaction logs might be different from the mdf files (pubs and pubs_log), so check for spelling.
The Path='\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\' is critical. The double slashes "\\" are required. We have about five varieties of this path, including varying paths on the same SQL Server. Be aware of this and change your paths to match.
TIP: If you copy this document to Word, the quotation marks in the code might not translate correctly.If you save as a txt using Word the quotation marks will have to be re-entered in Notepad or any text editor. You usually can see that the Notepad single quote and the Word single quote are visually different when viewed in Notepad. This traps a lot of folks who download scripts from the web. |
The rest of the code writes the collected info into html table code. The font color, size, background color can be changed to suit. I use the color shift when the threshold is exceeded to make it easy to identify a problem file, because of the number of databases we monitor.
Then the html code is closed and the file step is to open the Discovery.htm using a shell object. Go behind the curtain and the Wizard will present you with your Brain. All right so maybe it is just a piece of paper but you are now smarter. Right?
Final Foot Note: If you want to date code the htm file you can do the following: Under dtmDate=CStr(Now()) Add: dtmNewDate=Now() dtmYeDate=DatePart(yyyy,dtmNewDate) dtmMoDate=DatePart(mm,dtmNewDate) dtmMoDate=DatePart(dd,dtmNewDate) dtmFileDate=dtmYeDate & "-" & dtmMoDate & "-" & dtmDaDate strDatedFile="C:\Discovery" & dtmFileDate & ".htm" In the line Set objNewFile=objFSO.CreateTextFile("C:\Discovery.htm") Change it to read: Set objNewFile=objFSO.CreateTextFile(strDatedFile) |
Next Part 2 . . . The treasure map to folders.
