Очистка БД CRM от вложений

В CRM примечания присоединяются либо к записям пользователями, либо приходят с электропочтой. И зачастую эти вложения занимаю большую часть БД. Эта два вида вложений хранятся в двух разных таблицах (в формате Base64):

• Вложения в электропочту хранятся в таблице Attachment.
• Вложения в Примечания хранятся в таблице AnnotationBase.

Зачастую эти вложения занимают очень много места на диске. И больше всего – тело вложения, т.е. содержимое файла (это поля body и documentbody, соответственно).

Допустим мы хотим очистить базу от устаревших вложений. Но совсем удалять их не хотелось бы ведь это бизнес данные. Поэтому можно вместо файлов оставить «отметку» о том, что они раньше были и сообщение для пользователя, что эти файлы били удалены. Маневр заключается в замене в определенных файлах расширения на txt, а содержимого файла на простое текстовое сообщение в закодированном виде: «Файл был удален в августе 2017».

UPDATE
	Attachment
SET
	Body = '0KTQsNC50Lsg0LHRi9C7INGD0LTQsNC70LXQvSDQsiDQsNCy0LPRg9GB0YLQtSAyMDE3',
    FileSize = 68,
    MimeType = 'text/plain',
    FileName = FileName + '.txt'
WHERE
	AttachmentId = (
		SELECT
			ATT.AttachmentId
		FROM
			Attachment AS ATT
				INNER JOIN ActivityMimeAttachment AS XXX ON ATT.AttachmentId = XXX.AttachmentId
				INNER JOIN EmailBase AS EML ON EML.ActivityId = XXX.ObjectId
				INNER JOIN ActivityPointer AS ACT ON EML.ActivityId = ACT.ActivityId
		WHERE
			XXX.ObjectTypeCode = 4202
			AND ATT.FileSize > 68
			AND ACT.ModifiedOn < GETDATE() - 365
	)

Этот запрос «очищает» таблицу вложений в электропочту. Для этого отбираются записи, которым больше года.

Примечания:

• Перевести текст в кодировку Base64 можно с помощью следующего сервиса: https://codebeautify.org/base64-encode
• Посмотреть размеры отдельных файлов можно с помощью следующих запросов:

  SELECT top 1000
  	(FILESIZE/1024/1024) [FIZE SIZE IN MB],
  	FILENAME,
  	ISDOCUMENT
  FROM
  	ANNOTATIONBASE (NOLOCK)
  ORDER BY
  	[FIZE SIZE IN MB] DESC
  
  SELECT top 1000
  	(FILESIZE/1024/1024) [FIZE SIZE IN MB],
  	FILENAME
  FROM
  	ATTACHMENT (NOLOCK)
  ORDER BY
  	[FIZE SIZE IN MB] DESC
  

• Чтобы посмотреть какие таблицы (читай объекты) занимают больше всего места в БД CRM можно с помощью следующего запроса:

  SELECT 
      t.NAME AS TableName,
      p.rows AS RowCounts,
      CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
      CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB, 
      CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceGB
  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 
      TotalSpaceGB desc