Clearing Kentico Content Staging Tasks Manually

A Kentico site I was working on had a huge backlog of content staging tasks, dating back over a year. The total number of tasks in the queue was around 200,000. Deleting these tasks with the button in the CMSDesk proved to be a futile task, since it would have taken many hours for the task to run.

The staging tasks are held in the Staging_Task table in Kentico. However, there are a couple other tables linking to the tasks in order to hold extra information. When clearing out the tasks, you must also clear the Staging_Synchronization and Staging_SyncLog tables to maintain data integrity.

I wrote the following script for use on Kentico 7. It removes all the constraints on the Staging tables, truncates the tables, then re-scripts the constraints.

I have not tested this on Kentico 8. I have not looked into how much the database structure for staging tasks has changed in Kentico 8, and would not recommend running this script on a Kentico 8 database.
USE [DATABASE_NAME]
GO

/*
    Drop the constraints that link to the Staging_Task table
    =================
*/

ALTER TABLE [dbo].[Staging_Task] DROP CONSTRAINT [DEFAULT_Staging_Task_TaskServers]
GO

ALTER TABLE [dbo].[Staging_SyncLog] DROP CONSTRAINT [FK_Staging_SyncLog_SyncLogServerID_Staging_Server]
GO

ALTER TABLE [dbo].[Staging_SyncLog] DROP CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task]
GO

ALTER TABLE [dbo].[Staging_Synchronization] DROP CONSTRAINT [FK_Staging_Synchronization_SynchronizationTaskID_Staging_Task]
GO

ALTER TABLE [dbo].[Staging_Task] DROP CONSTRAINT [PK_Staging_Task]
GO

ALTER TABLE [dbo].[Staging_Task] DROP CONSTRAINT [FK_Staging_Task_TaskSiteID_CMS_Site]
GO


/*
    DELETE ALL THE THINGS!
    =================
*/
TRUNCATE TABLE Staging_Task
TRUNCATE TABLE Staging_Synchronization
TRUNCATE TABLE Staging_SyncLog


/* 
    Add Constraints back to the table 
    ================= 
*/

/* PK_Staging_Task */
ALTER TABLE [dbo].[Staging_Task] ADD  CONSTRAINT [PK_Staging_Task] PRIMARY KEY NONCLUSTERED 
(
    [TaskID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


/* FK_Staging_Task_TaskSiteID_CMS_Site */
ALTER TABLE [dbo].[Staging_Task]  WITH CHECK ADD  CONSTRAINT [FK_Staging_Task_TaskSiteID_CMS_Site] FOREIGN KEY([TaskSiteID])
REFERENCES [dbo].[CMS_Site] ([SiteID])
GO

ALTER TABLE [dbo].[Staging_Task] CHECK CONSTRAINT [FK_Staging_Task_TaskSiteID_CMS_Site]
GO

/* DEFAULT_Staging_Task_TaskServers */
ALTER TABLE [dbo].[Staging_Task] ADD  CONSTRAINT [DEFAULT_Staging_Task_TaskServers]  DEFAULT ('null') FOR [TaskServers]
GO



ALTER TABLE [dbo].[Staging_SyncLog]  WITH CHECK ADD  CONSTRAINT [FK_Staging_SyncLog_SyncLogServerID_Staging_Server] FOREIGN KEY([SyncLogServerID])
REFERENCES [dbo].[Staging_Server] ([ServerID])
GO

ALTER TABLE [dbo].[Staging_SyncLog] CHECK CONSTRAINT [FK_Staging_SyncLog_SyncLogServerID_Staging_Server]
GO

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

ALTER TABLE [dbo].[Staging_Synchronization] CHECK CONSTRAINT [FK_Staging_Synchronization_SynchronizationTaskID_Staging_Task]
GO

/* SyncLogTaskID FK in SyncLog*/
ALTER TABLE [dbo].[Staging_SyncLog]  WITH CHECK ADD  CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task] FOREIGN KEY([SyncLogTaskID])
REFERENCES [dbo].[Staging_Task] ([TaskID])
GO

ALTER TABLE [dbo].[Staging_SyncLog] CHECK CONSTRAINT [FK_Staging_SyncLog_SyncLogTaskID_Staging_Task]
GO

Written on September 5th, 2014 by Dan Walker

Want to read more?

If you’ve enjoyed what you’ve seen so far, you might like some of my other posts. Visit the archive to see all of my past writings.

Blog Archive »
 

About the Author

Dan Walker is a programmer from Grand Rapids, MI. He works at Gordon Food Service.

More About Dan »

Get in touch