We use a stored procedure to do this for incidents closed for more than 40 days.
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())
Thanks! I will try it out as soon as I am on site again. Cheers!
Your SQL Script worked. Thanks!
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?
Have you found away to calculate the size of the Attachments in the DB and how much the space saving should be?
No sure if this helps ( i pinched it off the 'net). It'll show you the size of the tables in your database
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
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows
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).
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
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.