Finance | Project Operations, Human Resources, ...

Document Attach - Database Size

(1) Share

Report

Posted on by SL_GP_BC_123

1,108

Anyone have any bench/marks for how document attach affects database size. I've found a few posts and articles for GP2013 and 2014, but nothing more current. Plea. se advise.

Look forward to a response. This would be for GP18.6

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Joseph Markovich 3,995 on at

Like (1)

Report
Copy link

Link copied!

Doc attach really hasn't changed at all in all these GP versions regarding database size. It's just going to bloat the database like crazy as time goes by. This is why I've never used it and never liked it.

I know there are third party add-ons out there for document management, but there is also the one thing in everyone's MS365 tenant: SharePoint. This is a perfect solution for attaching files to records.

At one client, have done a little bit of customization to their GP: a few modified screens to show an Attachment button, a little bit of VBA (I know, not supported anymore, but it still is for the life of Windows 10/11) and SharePoint. Even works with workflow emails -- just puts a link to the location in SharePoint instead of an attachment.

Have considered packaging this up into a solution.

Joe

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report
Copy link

Link copied!

VBA is not supported anymore but Dexterity can be used for this kind of requirement.

I have done similar customization for attachments in Sharepoint and local shared drive.

Was this reply helpful? Yes No
Suggested answer

Talon Meyer 91 on at

Like (1)

Report
Copy link

Link copied!

This would vary from company to company, here's a script I have used to get the size of all document attachments in a database.

SELECT
    t.name AS [Table] ,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB

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%'
    AND t.is_ms_shipped = 0
    AND i.object_id > 255
       and t.name = 'coAttachmentItems'
GROUP BY
    t.name, s.name, p.rows

You could look at CO00101 and CO00102 to find data you may want to purge, and be sure to delete the same attachment_id from all the Doc Attach tables.

Was this reply helpful? Yes No