Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Document Attach - Data...
Finance | Project Operations, Human Resources, ...
Suggested answer

Document Attach - Database Size

(1) ShareShare
ReportReport
Posted on by JGarcia123 1,599
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 91 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 11,003 User Group 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,900 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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans