Which tables can be safely truncated?

Ashutosh Pandey asked on April 19, 2020 07:11

What tables can be safely truncated? Our database size is at 12.9 gb

I ran following query to figure out table sizes:

SELECT
t.Name                                       AS TableName,
s.Name                                       AS SchemaName,
p.Rows                                       AS RowCounts,
SUM(a.total_pages) * 8                       AS TotalSpaceKB,
(SUM(a.total_pages) * 8/1024)                AS TotalSpaceMB,
(SUM(a.total_pages) * 8/(1024*1024))                AS TotalSpaceGB,
SUM(a.used_pages) * 8                        AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
TotalSpaceMB desc;

The tables with most data are:

TableName                   SchemaName  RowCounts   TotalSpaceKB    TotalSpaceMB    UsedSpaceKB
Staging_Task                dbo         157843      10775944        10523           10761864
CMS_ObjectVersionHistory    dbo         2550        677496          661             670600
OM_Activity                 dbo         472070      517120          505             382720
Analytics_HourHits          dbo         2054845     340704          332             312928
CMS_VersionHistory          dbo         20132       258808          252             247800
Analytics_DayHits           dbo         1418667     227808          222             198792
CMS_Relationship            dbo         1143306     210128          205             163568
OM_Contact                  dbo         184104      149296          145             119160
Analytics_WeekHits          dbo         652004      113632          110             89528
Analytics_MonthHits         dbo         260910      47904           46              35800
Analytics_Statistics        dbo         76595       22368           21              16672
Analytics_YearHits          dbo         101668      18528           18              13712
CMS_Document                dbo         5170        17616           17              12440
Media_File                  dbo         7899        17048           16              12576

Correct Answer

Brenden Kehren answered on April 20, 2020 15:42

Ashutosh, It appears like staging is the biggest problem. I'd start with that table first and get things cleaned up. Not only should you look in the Staging app but query the database table staging_task directly to get a record count as there could be orphaned tasks in there from adding/removing servers while tasks were being logged.

The second thing I'd look at would be analytics. Make sure they are being cleaned up so you don't have GB of data being stored in there. If you're not using it at all, stick with Google Analytics and simply clean the data up and turn it off in Kentico.

0 votesVote for this answer Unmark Correct answer

Recent Answers


Brenden Kehren answered on April 19, 2020 07:28

None of the tables you listed can SAFELY be truncated.

I'd first review the Content Staging tasks and either remover the staging tasks or sync them over to the other server.

Secondly, if you don't use web analytics, clean that data up through the UI and turn it off. This should help with the size of your database.

1 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 19, 2020 19:02

I'd second Brenden. You probably need to synchronize staging and development servers to get read of discrepancies. Or restore you production to dev (this way staging task will be empty.). The other thing you can do is shrink the DB. You might gain a lots of space, but you need to rebuild indexes after that.

1 votesVote for this answer Mark as a Correct answer

Ashutosh Pandey answered on April 20, 2020 15:32

Looks like I need to dig deeper on this. Will update my plans. Thanks

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.