This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Database Size Monitoring

Hi Everyone,

My apologies if this is a overly redundant question, but I haven't been able to find much about this, so here it goes, maybe someone around here with a bit more experience might be able to lend a hand.

I run several (20+) servers that run either SQL 2005 or 2008, and each one of these contains anywhere from 1 to 8 databases each. My goal is to monitor the database size itself, as I have autogrowth disabled, I am constantly having to grow databases manually, and I'd like solarwinds to alert me on percentage of database size utilization.

Does anyone know how I could accomplish this? I've tried several templates from within the APM presets, as well as some downloaded from the colaboration pool @ thwack and I still can't get what I need.

 

Anyone with ideas or suggestions??

 

Note: The boxes are monitored in NPM for uptime, and other component utilization, as well as some WMI service status flags and what not on APM, so I am pretty familiar with SW itself, its monitoring SQL where my weakness lies.

  • I am doing something similar by monitor MDF and LDF file size using APM. Have you looked at that option in APM?

  • Hi 'sotherls', thanks for taking the time to answer,

     

    When you say monitor mdf and ldf file size using apm, you mean you are using the file check template? Or did you make a custom script to check on file size? I saw that template and took a look, the thing though is that I have autogrowth on the databases disabled (for a matter of data integrity), so I have to grow the db's manually, so the file itself doesnt change unless I tell it too, but the space provisioned for the actual mdf file is what is consumed [i tend to babble alot, please let me know if i was unclear].

    I'm looking for a percentage, so that i can set thresholds for utilization and eventually configure alert manager to page me on that, something like this:

    "database file utilization for test_db is at 90%"

    So that I can then grow the database on SQL Management Studio before it's filled.

  • Yea, I am doing the file size check as ours is set to autogrow to avoid running out of space.

    Ok, so is there a command line or SNMP OID that can report on size? If so we may be able to script something. How do you see what size it is? Are you only able to see it within SS Studio?

  • If I could only set them to autogrowth, it would solve  this whole thing, lol.

     

    Anyhow, I've only successfully obtained this data via either sql query or manually checking on ss studio. Then again, I'm not a sql buff, did look through some values in the performance counter group in sw but didnt find anything that reported something I could intepret.

     

    The commands I use would be:

    SP_HELPFILE : to obtain logical name and file size
    ALTER DATABASE XXXX MODIFY FILE (NAME='XXXXXX', SIZE=YYYYYGB) : to change the db size

  • Hello,
    if you are able to get this data using SQL query, you can use "SQL Server User Experience Monitor" for this. This monitor requires you to specify a SQL query that returns one numeric value and you can then set alerts on that value.

    For example:

    SELECT database_size FROM sys.some_table WHERE database_name = 'test_db'

    Go to Admin->APM Settings->Create new template, add new component and select "SQL Server User Experience Monitor". Enter your query, set thresholds for warning and critical values (for example >80% warning, >90% critical) and finally set alerts on warning and critical statuses for your monitors.

  • Hi Jiri,

    Thanks for your response, my apologies for the late reply, I've been pretty busy.

    I am going to try what you posted and get back at you sometime in the next few hours and confirm if it works.

    Thanks for the help.

  • Hi,

    I'm not getting this to work.

    Your query does not work, I cant't find a sys. table that could deliver the DB size column...

    There is no simple answer on goolge for SQL querying a DB size, because the sp_spaceused approach does not work for APM, since it delivers more then one column...

    So how do you finally monitor SQL DB size in APM using SQL commands?

  • The following query should get you most of the way there. You'll simply need to filter the results so it only returns information for the database you're interested in.

    select db_name(dbid),str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles

    group by dbid order by 2 desc

    GO

  • yes this goes in the direction, I still have issues with the filter because the "where" clause seems not to work:

    select db_name(dbid) as DBname,str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles

    where DBname = 'SolarwindsNPM'

    group by dbid order by 2 desc  

    GO

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'DBname'.

  • In the following query replace 'master' with the name of your database.

    SELECT

      cntr_value

    FROM

      sys.dm_os_performance_counters

    WHERE

      object_name = 'SQLServer:Databases' AND

      counter_name = 'Data File(s) Size (KB)' AND

      instance_name = 'master';