Администрирование
16
Окт
0

Очистка БД 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
    
Комментарии (0)

*

code