Monitoring Drive and Database Free Space

Question

Monitoring Drive and Database Free Space

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 1

Hi Mark,

Thank you very much for writing back to me, even just before I go on holiday (today) as well. I have tried DBCC UPDATEUSAGE (dbname) locally, and found problems, so I expect that I'll find problems on the client site as well.

Best regards

Henrik

Tory Skyers Grasshopper Points: 22 More actions · Answer 2

Aaron, I use nagios, http://www.nagios.org, webmin, http://www.webmin.com, and nagmin http://nagmin.sourceforge.net. It is extensible, free, cross platform, and very powerful. It takes a little getting used configs to, the can get ugly. I use nagmin to manage my configs but after a bit of reading i'm sure you can make it work for you.

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 3

Hi Mark,

It works. Running DBCC UPDATEUSAGE works.

Thank you very much for your script, and for your help.

Best regards,

Henrik Staun Poulsen

chung-327878 SSC-Addicted Points: 432 More actions · Answer 4

Hi Rcarlson ,

Thanks for the VB script. It works great for me. I took the liberty to parametize the server name so that I can use for multiple servers and drives. (Sorry Aaron Myers, I am one step ahead of you.) Here is the modified script:

' Returns drive size results to an HTML file.

On Error Resume Next

' HTML coding

const H_HEAD = "<HTML><HEAD>"

const T_HEAD = "<TITLE>STATUS REPORT</TITLE></HEAD>"

const B_HEAD = "<BODY BACKGROUND=one.jpg><CENTER><H3>SERVER DISK SPACE UTILIZATION REPORT - - DATE: "

const H_CLOSE = "</CENTER></BODY></HTML>"

' HTML TABLE CODES

const S_TABLE = "<TABLE BORDER=4 CELLPADDING=3>"

const T_TABLE = "<TR><TH>FILE NAME</TH><TH>STATUS</TH></TR>"

const E_TABLE = "</TABLE><BR>"

' HTML SUCCESS OR FAILURE CODE

const F_SUCCESS = "<TD ALIGN=CENTER><FONT COLOR=GREEN>OK</FONT></TD></TR>"

const F_FAILED = "<TD ALIGN=CENTER BGCOLOR=WHITE><FONT COLOR=RED SIZE=+1><B>FAILED</B></FONT></TD></TR>"

' Conversion codes Bites to Megabytes and 2000 megabytes threshold

const CONVERSION_FACTOR = 1048576

const WARNING_THRESHOLD = 2000

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objNewFile = objFSO.CreateTextFile("statusSize.htm")

objNewFile.WriteLine H_HEAD

objNewFile.WriteLine T_HEAD

objNewFile.WriteLine B_HEAD & Now() & "</H3>"

objNewFile.WriteLine S_TABLE

'*** Start building display rows ***

objNewFile.WriteLine "<TR><TD>SERVER</TD> <TD>DRIVE</TD> <TD>DESC</TD> <TD>Total Size MB</TD> <TD>FREE MB</TD> <TD>STATUS</TD></TR>"

'--- Enter as many server names and drives as you like ---

Call BuildRow("servername", "C:", "Windows")

objNewFile.WriteLine "<TR></TR>"

Call BuildRow("servername", "E:", "Application")

'-------------------------------------------------------

objNewFile.WriteLine E_TABLE

objNewFile.WriteLine H_CLOSE

const MAXIMIZE_WINDOW = 3

Set objNetwork = Wscript.CreateObject("Wscript.Network")

Set objShell = Wscript.CreateObject("Wscript.Shell")

objShell.Run "statusSize.htm", MAXIMIZE_WINDOW

STOP

Sub BuildRow(pvsComputer, pvsDriveLetter, pvsDriveDesc)

Dim FreeMegaBytes

Dim SizeMegaBytes

Dim objWMIService

Dim colProcesses

Dim objLogicalDisk

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

Set colProcesses = objWMIService.ExecQuery _

("SELECT * FROM Win32_Process")

'-- Drive Letter --

'------------------

Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='" & pvsDriveLetter & "'")

FreeMegaBytes = objLogicalDisk.FreeSpace / CONVERSION_FACTOR

SizeMegaBytes = objLogicalDisk.Size / CONVERSION_FACTOR

If FreeMegaBytes < WARNING_THRESHOLD Then

strColor = "RED"

Else

strColor = "YELLOW"

End If

objNewFile.WriteLine "<TR BGCOLOR=" & strColor & " >"

objNewFile.WriteLine "<TD>" & pvsComputer & "</TD>"

objNewFile.WriteLine "<TD>" & pvsDriveLetter & "</TD>"

objNewFile.WriteLine "<TD>" & pvsDriveDesc & "</TD>"

objNewFile.WriteLine "<TD>" & Int(SizeMegaBytes) & "</TD>"

objNewFile.WriteLine "<TD>" & Int(FreeMegaBytes) & "</TD>"

If strColor = "RED" Then

objNewFile.WriteLine "<TD>" & "LOW" & "</TD>"

Else

objNewFile.WriteLine "<TD>" & "OK" & "</TD>"

End If

objNewFile.WriteLine "</TR>"

'*** End building display rows ***

End Sub

Hagop Hagopian SSC Rookie Points: 37 More actions · Answer 5

it seems that none of the links in the article are working.. could you please fix... thanks

Bannji Kumoye Newbie Points: 7 More actions · Answer 6

yes i have thesame problem overhere, none of the links are working rightnow, whats up?

Andries Vermaak-433333 SSC Veteran Points: 229 More actions · Answer 7

The links to the Stored Procs are broken. Does anybody stil have the code or a new link to the SP's

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 8

Good morning everyone,

I want to implement jobs from the article. With first one I had no problem - Drive Space Monitoring - however my job with the database space info is failing. When I run manually one by one there is no problem, when I start a job then the second step (Capture Space Info on all DBs) finishes with the error message"

String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Do you have any ideas what can cause the problem? I run it under SQL Server 2005

Regards,

Kasia

11.01.08

I have turned off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF. Now job runs with success, however I am not sure if I am not missing any data..

k.przewozniak Ten Centuries Points: 1133 More actions · Answer 9

Hello All,

Can you tell me if the procedures need update for SQL Server 2005?

paksyeet SSCommitted Points: 1596 More actions · Answer 10

Hi Chung

how will i execute your script?!?;p

how can i view the results.. thanks

Dave Myers-429047 SSC Rookie Points: 29 More actions · Answer 11

I have installed this into our TEST environment and when I execute the dbo.sp_Monitor_Capture_DB_Space_Info procedure

Msg 8152, Level 16, State 2, Line 1

String or binary data would be truncated.