How to clear staging task table?

Ashutosh Pandey asked on May 1, 2020 09:02

Out of 12 GB of database size, 10 GB is consumed by Staging_Task table.

I need to understand, what causes this table to grow like this. What should I do to limit this? How can I clear it?

I got this script on devnet but looks like it is for the older versions.

BEGIN TRANSACTION
BEGIN TRY

ALTER TABLE [dbo].[Staging_SyncLog] DROP CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task]
ALTER TABLE [dbo].[Staging_Synchronization] DROP CONSTRAINT [FK_Staging_Synchronization_SynchronizationTaskID_Staging_Task]


truncate table staging_synchronization
truncate table staging_synclog
truncate table staging_task

ALTER TABLE [dbo].[Staging_SyncLog]  WITH CHECK ADD  CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task] FOREIGN KEY([SyncLogTaskID])
REFERENCES [dbo].[Staging_Task] ([TaskID])
ALTER TABLE [dbo].[Staging_SyncLog] CHECK CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task]

ALTER TABLE [dbo].[Staging_Synchronization]  WITH CHECK ADD  CONSTRAINT [FK_Staging_Synchronization_SynchronizationTaskID_Staging_Task] FOREIGN KEY([SynchronizationTaskID])
REFERENCES [dbo].[Staging_Task] ([TaskID])
ALTER TABLE [dbo].[Staging_Synchronization] CHECK CONSTRAINT [FK_Staging_Synchronization_SynchronizationTaskID_Staging_Task]

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH   


IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

Does it grow because somebody has checked out pages and didn't checked in. Please help, thanks

Correct Answer

Brenden Kehren answered on May 1, 2020 21:14

Content Staging allows you to move content from one Kentico environment to another Kentico environment. For instance dev.domain.com - > domain.com. Nearly every change is tracked within Kentico and stored as a staging change in the staging tables. When the content is moved from the source server to the target server, the data is removed from the staging tables.

First, you want to make sure you're not using content staging. If you're not, you can proceed to clean up the tables associated with those tables. To better understand what relationships those tables have you should review the database schema of the Staging_<tablename> tables. See the attached below and you can write your script(s) for deletion around it. There is no way to truncate that I'm aware of due to the FK/PK constraints. Removing the constraints could have a negative impact on your system even if you attempt to add them back.

As always, perform your deletion tests after you've performed a backup and only on a development/test database.

Image Text

4 votesVote for this answer Unmark Correct answer

Recent Answers


Dmitry Bastron answered on May 1, 2020 09:43

Hi Ashutosh,

Do you use Content Staging? Do you see outstanging tasks in Staging Application? Refer to manage Staging documentation. One of potential problems is that you are actually not using Staging but it is enabled and keeps tracking changes as tasks.

2 votesVote for this answer Mark as a Correct answer

Ashutosh Pandey answered on May 2, 2020 05:58

Thanks @Brenden for the detailed explanation.

I checked the following link:

https://docs.kentico.com/k12/deploying-websites/content-staging

I went to the staging application and found one instance mentioned there. So I assume because of that, the Staging table grew in size. Also, the mentioned instance in Staging application was non-functional.

So, here are my questions:

  1. If I remove that instance from the staging application, will it impact our current site?

  2. Do I have to still clear staging tables manually?

I will try to test it by downloading the database on my local.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on May 2, 2020 06:13

Ashutosh,

You can attempt to delete the tasks and/or the server but if your SQL server doesn't have the power it will timeout. I'd suggest deleting pages of tasks first and see if they delete successfully or not. If they do, you can try to delete the server and it should delete the associated tasks.

You'll then want to check the tables to make sure there aren't any orphaned records.

2 votesVote for this answer Mark as a Correct answer

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