Get details on the size of tables and space used by raw data in UIM Database

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Currently we have 60 days of Raw data. We have had some low disk on the SQL server due to the size of our database and are considering lowering the Raw data retention time. How can we calculate how much space we would save by changing this from 60 to 45 or 30 days?

Environment

Release : 20.3

Component : UIM - DATA_ENGINE

Resolution

You can use the following to capture the list of your RN tables with the amount of data used, and throw that into Excel, and add a total to the UnusedSpaceMB, from that you can multiply that by the % you want to keep or cut to get your savings.

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
-- WHERE t.NAME NOT LIKE 'dt%'
WHERE t.NAME LIKE '%RN_QOS_DATA_%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name