Skip to main content
Microsoft Dynamics GP forum
Suggested answer

Document Attach - Database Size

editSubscribe (1) ShareShare
ReportReport
Posted on by 1,330
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
 
JG
  • Suggested answer
    Talon Meyer Profile Picture
    Talon Meyer 90 on at
    Document Attach - Database Size
    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.
  • Suggested answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 10,956 UG Leader on at
    Document Attach - Database Size
    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.
  • Joseph Markovich Profile Picture
    Joseph Markovich 3,753 on at
    Document Attach - Database Size
    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

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,068 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,591 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans