Monitoring Drive and Database Free Space

  • markenash

    Mr or Mrs. 500

    Points: 507

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist

  • Fred Salchli

    Grasshopper

    Points: 15

    Where's the beef? Most of the code to utilize this outstanding monitoring system is not included! Please make source available.

     

    Thanks!

  • Kishore.P

    SSCrazy Eights

    Points: 8147

    Some stored procedures were missed, like

    EXEC msdb..sp_Monitor_Capture_DB_Space_Info

    EXEC msdb.dbo.sp_Monitor_Email_Low_DB_FreeSpace 0.1

    EXEC msdb..sp_MSSQL_Recovery_Info

  • Joan OBryan

    Mr or Mrs. 500

    Points: 510

    This article is a good read, but would be much more useful if the rest of the code was included.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716239

    Apologies!

    My fault. Mark asked me to link it in and I forgot . It's up there now.

  • Joan OBryan

    Mr or Mrs. 500

    Points: 510

    Awsome. Thanks, Steve. Can you also change my vote to "Excellent" for me?

  • Sree-121435

    Grasshopper

    Points: 21

    I have followed all steps but couldn't able to produce the report when i ran sp_DASD_Report it only shows servername and list of databases on that particular server.

    Is there anyting need to be modified for the given code?

     

    Thanks,

     

  • markenash

    Mr or Mrs. 500

    Points: 507

    Good morning Sree,

    I am sorry to hear you have had problems. Here are the points I check first.

    If the dates on the report are all '20201225' (December 25, 2020) then you are just reporting the 'separator' row. I put in this row just to create a break between databases. I figured I would never be using this system in 2020 🙂

    - Do a TSQL select on the DASD table and just make sure there are rows in it other than the above separator rows.

    - We run the job that populates the DASD table once daily, between 04:00 and 04:59 on all servers. We found that reporting on all days of the week was too much detailed information for a general report, so we limited it to just report DASD rows created on Friday between 04:00 - 04:59.

    Possibly the issue in the  'insert into #DASDRpt' statement.  The 'select' in this statement was designed to only select rows created on

    1) Friday  "datepart(dw,(convert(....)))) = 6" 

    2) created during hour '04'  "substring(createDTM,9,2) = '04'"

    If you have not scheduled the job that creates DASD rows to run at least on Friday between 04:00 - 04:59 you will not return any rows.

    If the date, time selects are the issue, modify the report sql as you wish.

    Let me know if this information helps, if not we will look further!!

  • rscarlson

    SSC Eights!

    Points: 856

    I found this an easier approach

    I run the following batch file at logon:

    cscript status.vbs

    rem pause

    The status.vbs is as below, you need admin access, the name of the computer and a drive mapped to same, and the drive letter of the drive you want to read the size of.  This returns a web page with the drive size in mega bytes and alerts if less than 2 gigabytes (the threshold = 2000).   I monitor several databases with this script and the web page shows at a glance potential trouble spots (just repeat the code for other dbs).

    ' 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>STATUS 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

    strComputer = "."

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

    Set colProcesses = objWMIService.ExecQuery _

     ("SELECT * FROM Win32_Process")

    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 "<FONT COLOR=PURPLE><B>DATABASE SPACE ON DRIVE E:</B></FONT>"

    objNewFile.WriteLine S_TABLE

    Computer = "ComputerName"

    Set objWMIService = GetObject("winmgmts://" & Computer)

    Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='e:'")

        FreeMegaBytes = objLogicalDisk.FreeSpace / CONVERSION_FACTOR

        If FreeMegaBytes < WARNING_THRESHOLD Then

            objNewFile.WriteLine "<TR><TD BGCOLOR=WHITE><FONT COLOR=RED SIZE=+1><B>INADEQUATE</B></FONT></TD>"

            objNewFile.WriteLine "<TD BGCOLOR=WHITE>Actual Space " & Int(FreeMegaBytes) & " Megabytes.</TD></TR>"

        Else

           objNewFile.WriteLine "<TR><TD BGCOLOR=CCFFCC>ADEQUATE</TD>"

           objNewFile.WriteLine "<TD BGCOLOR=CCFFCC>Actual Space " & Int(FreeMegaBytes) & " Megabytes.</TD></TR>"

    End If

    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

     

  • markenash

    Mr or Mrs. 500

    Points: 507

    Good morning rscarlson,

    Our space monitoring system was presented in the article for review and evaluation by fellow DBA's. Those that deemed it useful were free to install all or part of it at their sites. This system fits our institutions needs for space management.

    You have developed your own system for space management, we are glad it fulfills your sites needs.

     

  • Aaron Myers

    Ten Centuries

    Points: 1139

    Thanks for the script Rcarlson! It doesn't quite do the same thing but it is more useful in general (non sql server administration). I think I'll parameterize the inputs so there will be nothing hardcoded inside the script.

  • Tory Skyers

    Grasshopper

    Points: 22

    I'm an SQL newbie but very familiar w/ os level scripting, WMI can be costly be careful where and when you use it! . Your script is great btw, i use a similar script locally on some servers to monitor and kick out to an opensource system monitor however i've found that certain WMI queries can be taxing on systems.

    Using a native to the db call on the system the db is running on in theory should be less costly cpu/resource wise than using wmi. Then there is wmi security, network transport security etc, all of which you avoid by using the db system service to run the queries.

    On a semi-related note, did you use wbemtest to find the properties of the disk device? I've been having some trouble locating particular wmi attribute detail. Do you know if sql publishes info via wmi?

  • Aaron Myers

    Ten Centuries

    Points: 1139

    I totally agree, Mark's monitor is great for efficiently tracking individual DB's inside SQL. I get a little nervous modifying MSDB or running XP procedures so there is a little tweaking I may do there to isolate the security and data.

    Tory, what is the open source system monitor you are using? I've been playing around with Perfmon logging directly to SQL, and building some of my own ASP.NET to tie the data together in web pages.

    http://aaronsmyers.blogspot.com/2005/04/nlogger-update.html

    If there is already something out there to help, by all means I'd like to give it a try. MOM is just too expensive to justify for most of our servers/applications.

  • Henrik Staun Poulsen-237657

    Old Hand

    Points: 316

    Mark,

    I'm getting a negative free_DB_Space for two of our databases. When running these two queries:   select size, maxsize 'maxsize' from sysfiles where (status & 64 = 0) compute sum(size)

    select sum(reserved) 'totalreserved'  from [sysindexes] where indid in (0, 1, 255) I get

    size        maxsize    

    ----------- -----------

    319872      -1

    9600000     -1

    109120      -1

    9487992     -1

    ===========

    19516984

    totalreserved

    -------------

    20830649

    So the sum from sysindexes is bigger than sysfiles. Is this because we're using filegroups on these databases?

    TIA

    Henrik Staun Poulsen, Denmark

     

  • markenash

    Mr or Mrs. 500

    Points: 507

    Hello Henrik,

    Sorry for the slow response, been on vacation since  7/2/05.

    Whenever we get negative numbers for free_DB_Space there has been a problem with sysindexes.  Try running DBCC UPDATEUSAGE (dbname) and see if you get positive numbers..

Viewing 15 posts - 1 through 15 (of 29 total)

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