8 Replies Latest reply on Sep 20, 2018 5:17 AM by Erdinc

    how to archive/delete attachments?

    KaiEilert Apprentice

      Hi all,

       

       

      I tried deleting the entries in the attachment action table and the data in "tps_attachment_data" table.

      However after that I could not log into Service Desk anymore and had to perform a rollback :-/

       

      Is there a way to delete attachment data from the Service Desk system?

       

      Thanks,

      Kai

        • 1. Re: how to archive/delete attachments?
          Expert

          We use a stored procedure to do this for incidents closed for more than 40 days.

           


          update tps_attachment_data

              set tps_data = 0x4172636869766564206174746163686D656E742E

              where tps_guid in

              (

              select substring(im_attachment, 49,36)

              from dbo.im_incident_attachment, im_incident, im_incident_closure

              where dbo.im_incident_attachment..im_incident_guid = im_incident..pm_guid

              and im_incident_closure.im_incident_guid = im_incident..pm_guid

              and im_incident_closure.im_creation_date < dateadd(day, -40, getdate())

              )

          • 2. Re: how to archive/delete attachments?
            KaiEilert Apprentice

            Thanks! I will try it out as soon as I am on site again. Cheers!

            • 3. Re: how to archive/delete attachments?
              KaiEilert Apprentice

              Hey Chris,

               

              Your SQL Script worked. Thanks!

              • 4. Re: how to archive/delete attachments?
                KaiEilert Apprentice

                Hello Chris,

                 

                After a few months of using the Script I noticed that the script does not make the database smaller. It deletes the reference to the attachment. However the database size stays the same.

                Do you have the same behavior?

                 

                Kind Regards,

                Kai

                • 5. Re: how to archive/delete attachments?
                  Apprentice

                  Hey Chris,

                   

                  Have you found away to calculate the size of the Attachments in the DB and how much the space saving should be?

                   

                  Thanks,

                  Dan

                  • 6. Re: how to archive/delete attachments?
                    jasoncadman Expert

                    No sure if this helps ( i pinched it off the 'net). It'll show you the size of the tables in your database

                     

                    SELECT

                        t.NAME AS TableName,

                        s.Name AS SchemaName,

                        p.rows AS RowCounts,

                        SUM(a.total_pages) * 8 AS TotalSpaceKB,

                        SUM(a.used_pages) * 8 AS UsedSpaceKB,

                        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

                    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

                    GROUP BY

                        t.Name, s.Name, p.Rows

                    ORDER BY

                        UsedSpaceKB desc

                    • 7. Re: how to archive/delete attachments?
                      mushoku Apprentice

                      I don't know how much it matters, there is additional info for the attachment that remains - File name, size, and MIME info.  I created an attachment that I wanted the deleted attachments to become and pulled the info from the database to determine the correct info for replacing.  Our stored procedure is built more for running on demand, no issues with space (yet).

                       

                      Usage examples:

                      sp_DeleteAttachment 12345, 2 (will replace attachment 2 on ticket 12345 with file "Deleted Attachment.txt" stating "Attachment deleted")

                      sp_DeleteAttachment 12345 (will replace attachment 1 on ticket 12345...)

                       

                      create procedure sp_DeleteAttachment
                      @id INT,
                      @attachserial INT = 1
                      as
                      begin
                      
                          declare @attachguid uniqueidentifier
                          declare @dataguid uniqueidentifier
                      
                          select @attachguid = im_incident_attachment.im_guid, @dataguid = substring(im_attachment, 49,36) from im_incident_attachment join im_incident on im_incident_attachment.im_incident_guid = im_incident.pm_guid where im_id = @id and im_serial_number = @attachserial
                      
                          update im_incident_attachment
                              set im_attachment = CONCAT(substring(im_attachment,1,166),'Deleted attachment.txt</FileName><Size>18</Size><Type>None</Type><MimeType>text/plain</MimeType></DataObjectAttachment>' COLLATE SQL_Latin1_General_CP1_CI_AS)
                              where im_guid = @attachguid
                      
                          update tps_attachment_data
                              set tps_data = 0x4174746163686D656E742064656C65746564
                              where tps_guid = @dataguid
                      
                      end
                      
                      • 8. Re: how to archive/delete attachments?
                        Erdinc Apprentice

                        I once deleted all attachments, restored an OOTB db and copied the OOTB attachments back into my own db. Was able to log in just fine.