Strange question, but many Agresso databases contain more blob data than they do financial data.
What is blob data? Generally speaking it refers to files that are stored in the database such as JPEG’s, PDF’s text files, Word docs etc, and these may be scanned invoices, bacs remittances, pay slips, log files, report results files and so on.
The two main blob tables are ACRPRINTBLOB and ADSFILEBLOB. The former holds the server logging and report results files, and the latter is for scanned invoices etc.
It isn’t uncommon to find databases that over 80% blob data. This isn’t an issue in itself, but it’s useful to keep a watch on things to ensure that images are not being scanned at very high quality which produces large files. It also allows for future planning in regards to clearing down the ACRPRINTBLOB table or if you want to store the contents of ADSFILEBLOB outside the database in a Windows file share.
On SQL Server based systems it’s relatively easy to determine the size of these tables as you can just use the command SP_SPACEUSED in a SQL Server query window. Selecting the Agresso database and running the command on its own shows the size of the database itself.
Running the command followed by a table name shows the size of the table.
exec sp_spaceused acrprintblob exec sp_spaceused adsfileblob
Or if you just want to run a single command to list all of the data at once in MB, then there are several ways of doing this, such as the SELECT statement below
DECLARE @t TABLE (tableName SYSNAME, numRows BIGINT, reservedMB VARCHAR(32), dataMB VARCHAR(32), indexMB VARCHAR(32), unusedMB VARCHAR(32)) INSERT @t EXEC sp_spaceused acrprintblob INSERT @t EXEC sp_spaceused adsfileblob SELECT DB_NAME() as 'Name', '-' as 'Number of Rows', SUM(CONVERT(DECIMAL(10,2),(1.0 * FILEPROPERTY(name,'SpaceUsed')/128.0))) as 'Data – MB' FROM dbo.sysfiles UNION SELECT tableName as 'Table Name', numRows as 'Number of Rows', CAST(REPLACE(dataMB, 'KB','') as INT) / 1024 + CAST(REPLACE(indexMB, 'KB','') as INT) /1024 as 'Data – MB' FROM @t ORDER BY 'Data – MB' DESC
Which will show the following