OperationsManager Database alert for less than 40% free space RRS feed

  • Question

  • Hi,

    I am currently tuning SCOM. An alert for OperationsManager database is less than 40% reappears every few weeks.

    We currently have OpsDb sized at 8GB and Transaction  Log as 4GB.

    4.7Gb of the Operations Db is always in use, when it goes below 40% threshold I manually run the grooming process 62 times as per Kevin Holman, link below. This usually frees up another couple of %.


    When I run a large table Query on the OperationsDatabase as per 


    and add up all the table values, I get a value equivalent to 1.6GB.

    My questions are:

    Is there a way to investigate what else is taking up space?

    Should these table values added equal the database usage total?

    If so how to proceed?

    • Edited by Brianary1010 Tuesday, October 10, 2017 12:24 PM missing text
    Tuesday, October 10, 2017 12:22 PM

All replies

  • You can run the following query to find the top table space
    SELECT TOP 1000 
    a2.name AS [tablename], (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.rows as row_count, a1.data * 8 AS data, 
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size, 
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused, 
    (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1, 
    a3.name AS [schemaname] 
    FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows], 
    SUM (ps.reserved_page_count) AS reserved, 
    SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
    SUM (ps.used_page_count) AS used 
    FROM sys.dm_db_partition_stats ps 
    GROUP BY ps.object_id) AS a1 
    LEFT OUTER JOIN (SELECT it.parent_id, 
    SUM(ps.reserved_page_count) AS reserved, 
    SUM(ps.used_page_count) AS used 
    FROM sys.dm_db_partition_stats ps 
    INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
    WHERE it.internal_type IN (202,204) 
    GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
    WHERE a2.type <> N'S' and a2.type <> N'IT'

    Tuesday, October 10, 2017 1:55 PM
  • Thank you for your reply Sriraam,

    I have ran this query. 

    For example in the top table ManagementPack "Reserved" figure is 269616.

    If so when I add all the tables reserved space (1447 tables have reserved figures) I get a figure of 1543264.

    What do the figures represent? Is it page files or data in Kb,Mb?

    If page files how to convert to physical space used(Gb)?

    I ran this Query as well

    --Simple query to display large tables, to determine what is taking up space in the database:
    SELECT so.name, 
    8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, 
    Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb, 
    Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb 
    FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id) 
    WHERE 'U' = so.type GROUP BY so.name  ORDER BY data_kb DESC 

    This gives Tables with values for data_kb and index_kb. When I add all these figures I get a Figure of 1504776Kb which is equivalent to 1.5Gb.

    The Database currently has 4.7Gb consumed, I guess what I am asking is, if the tables aren't taking up excessive space, what might be consuming the space and where to look for a fault?

    We currently have <100 agents (around 70 and 4 agentless managed) and < 30 network devices monitored.

    • Edited by Brianary1010 Wednesday, October 11, 2017 9:38 AM grammar error
    Wednesday, October 11, 2017 9:21 AM
  • Okay, figures are in Kb. So tables and index all add up to <1.5Gb yet database has 4.7Gb consumed!?

    I have edited the grooming settings from 3/4 days to 2 days retention and will see if this clears down any data tomorrow.

    The recovery model of the database is set to Full as SQL is in an always on configuration.


    Thursday, October 12, 2017 9:13 AM