12-20-2023 10:33 AM
Is there any SQL query or report that tells me how much disk space is consumed by the documents in my system?
I need to know what my current used space is to plan if I need more space in the future. (without having to select each disk group in the plater and run the disk space analyzer with right click)
05-24-2024 09:26 AM
It makes me sad when I see questions that *I know* almost every admin has, go without answer in these forums. Back when we first implemented in 2016, the user community was well supported by Hyland staff, and seemed more active from users. The subsequent redesign really seemed to tank it. :sigh:
In case anyone else finds this, I'll leave a little crumb of my own. Configuration reports, such as Disk Group Configuration Report, are worthless.
So here's what I do. We're an Oracle shop, adjust as needed.
Query your db (we have a read only copy so there's no concern about performance impact).
-- Run once for each year; take these values and pop them into an Excel for graph/chart
SELECT
TO_CHAR(d.datestored, 'YYYY-MM') AS month_year,
ROUND(SUM(p.filesize)/1048576,2) AS total_size -- value in db is in bytes, turn it into kb
FROM
hsi.itemdatapage p
JOIN hsi.itemdata d ON p.itemnum = d.itemnum
WHERE
TO_CHAR(d.datestored, 'YYYY') = '2024' -- limiting by year, otherwise query bombs
GROUP BY
TO_CHAR(d.datestored, 'YYYY-MM')
ORDER BY
TO_CHAR(d.datestored, 'YYYY-MM');
Pop these values into Excel and make a pivot/chart/graph whatever you want to do.
05-26-2024 10:54 PM
Note: the query excludes the System DG so you can comment it out if you wish to include in the results. It also excludes deleted documents.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT RTRIM(dg.diskgroupname) AS 'Disk Group', COUNT(DISTINCT idp.itemnum) AS '# Documents', COUNT(idp.itemnum) AS '# Pages', CAST(SUM(idp.filesize / 1024) AS BIGINT) AS 'Space Used (KB)', CAST(SUM(idp.filesize / 1024) AS BIGINT) / 1024 AS 'Space Used (MB)', CAST(SUM(idp.filesize / 1024) AS BIGINT) / (1024 * 1024) AS 'Space Used (GB)', CAST(SUM(idp.filesize / 1024) AS DECIMAL(18, 2)) / (1024 * 1024 * 1024) AS 'Space Used (TB)'FROM hsi.itemdata iINNER JOIN hsi.itemdatapage idp ON i.itemnum = idp.itemnumINNER JOIN hsi.diskgroup dg ON idp.diskgroupnum = dg.diskgroupnumINNER JOIN hsi.doctype dt ON i.itemtypenum = dt.itemtypenumWHERE idp.physicalpagenum = 0 AND i.itemtypegroupnum NOT IN (1) -- Excluding System DTG AND i.status <> 16 -- Excluding deleted Docs (if you want to include deleted docs comment this line out)GROUP BY dg.diskgroupnameORDER BY dg.diskgroupname;
Regards,
George
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.