Monitoring Drive and Database Free Space

  • Henrik Staun Poulsen-237657

    Old Hand

    Points: 316

    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

    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: 316

    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

    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

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

  • Bannji Kumoye

    Newbie

    Points: 7

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

  • Andries Vermaak-433333

    SSC Veteran

    Points: 229

    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

    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

    Hello All,

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

  • paksyeet

    SSCommitted

    Points: 1596

    Hi Chung

    how will i execute your script?!?;p

    how can i view the results.. thanks

  • Dave Myers-429047

    SSC Rookie

    Points: 29

    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.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 2, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8115, Level 16, State 2, Line 1

    Arithmetic overflow error converting expression to data type int.

    The statement has been terminated.

    Msg 8152, Level 16, State 2, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146

    String or binary data would be truncated.

    The statement has been terminated.

    I receive the below errors when I execute the sp_HH_MSSQL_Recovery_Info procedure

    Msg 208, Level 16, State 1, Procedure sp_HH_MSSQL_Recovery_Info, Line 56

    Invalid object name 'master..sysfiles1'.

    And finally when I run the DASD_report , there is no size data (probably because the other procedures failed to populate completely)

    Any assistance in

  • jim.smith

    Valued Member

    Points: 50

    Hi There

    I'm also trying to get this to work in SQL 2005 and am struggling. If anyone has documented what needs to change in the scripts for this to work in the 2005 environment that would be great!

    Thanks in advance.

  • merchiem

    SSC Rookie

    Points: 41

    hey Mark,

    I read this Article and i am trying to implement it, however the Report is coming up with the database name only. 1. How do i get all the information in the database. 2. How do i go about changing the date to run lets say Tuesday at 1:00 PM in the sp_DASD_Report.sql.

    Thanks in advance

  • Siten0308

    SSCarpal Tunnel

    Points: 4024

    Hello,

    Thank you for the code, the only problem I am having with this is the:

    from msdb..DASD

    what table is that? where did you get that from, its throwing the error message 'invalid object name 'msdb..DASD'

    thanks again

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

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