how to get the total image file size from database

[Naveen_Bhat2]

is there any specific database table, to get the total image file sizes in OnBase using sql server. i create a report, where i have to give the size of the image files for every month.

[Thomas_Reu]

download the Database Reporting Guide- you can't miss

[Jonna_Pantelis]

is there any specific database table, to get the total image file sizes in OnBase using sql server. i create a report, where i have to give the size of the image files for every month.

Hi Naveen, hsi.itemdatapage has the filesize column. I use the following to determine if there are any files with 0 as the filesize but you can adjust this to pull just the sizes without the where statement. Hopefully this is helpful to you!

SELECT
hsi.doctype.itemtypename AS [Document Type],
hsi.itemdata.itemname AS [Document Auto-Name],
hsi.itemtypegroup.itemtypegroupname AS [Document Type Group],
hsi.itemdatapage.filesize AS [File Size],
--COUNT(hsi.itemdatapage.filesize) AS [File Size],
hsi.itemdata.itemnum AS [Document Handle],
hsi.itemdatapage.physicalpagenum

FROM
hsi.itemdata INNER JOIN
hsi.doctype ON hsi.itemdata.itemtypenum = hsi.doctype.itemtypenum INNER JOIN
hsi.itemtypegroup ON hsi.doctype.itemtypegroupnum = hsi.itemtypegroup.itemtypegroupnum INNER JOIN
hsi.itemdatapage ON hsi.itemdata.itemnum = hsi.itemdatapage.itemnum

WHERE
(hsi.itemdatapage.filesize = 0)

[Kevin_Granger]

Curious, after finding files with a zero size are you then purging any files of zero size for maintenance?

[Khaleil_Al-Sahs]

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT dg.diskgroupname AS 'Disk Group',
COUNT(idp.itemnum) AS '# Documents',
COUNT (idp.itemnum) AS '# Pages',
CAST (SUM(idp.filesize/1024/1024/1024) AS BIGINT) AS 'Space Used (GB)'
FROM hsi.itemdata i
INNER JOIN hsi.itemdatapage idp ON i.itemnum = idp.itemnum
INNER JOIN hsi.diskgroup dg ON idp.diskgroupnum = dg.diskgroupnum
WHERE idp.physicalpagenum = 0
AND i.datestored BETWEEN '2000-04-01' and '2021-04-30'--Change the date range.
GROUP BY dg.diskgroupname
ORDER BY dg.diskgroupname