Очистка БД 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