How to reduce size of profile DB

  • KM03813663
  • 11-Jun-2021
  • 11-Jun-2021

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

Customer has not enabled partition and purging and face the issue about size of data is big and almost full of Disk

Question

How to reduce size of profile database.

Answer

To reduce size of profile DB we need to enable partition and configure purging for tables.

The important thing is that we have to know what tables consuming large parts of database server space.

Step 1: connect to Profile DB

Step 2: run the query below to get top biggest 20 tables

sELECT top 20
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) desc
Step 3: configure purging for tables in step 2
Step 4: Waiting for few hour and see how size is reduced.