MS SQL Server and Oracle: How to remove all shared drafts and start with a clean state using Collaborative Editing

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Please read the parent page (How to remove all shared drafts and start with a clean state using Collaborative Editing) before proceeding. This set of queries is for MS SQL Server and Oracle.

Since there's no way to bulk delete shared drafts from the UI, the Confluence administrator should perform this operation from the database level.

This KB will walk you through the necessary steps to delete shared drafts and any of their associated contents, such as attachments.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

 Before starting, we can identify all shared drafts which are going to delete after following further steps. Below is the SQL that will help you make a decision for removing the shared drafts from the system so that we will be aware about that:

1. Stop Confluence following your standard procedure.

  • If running Confluence data center, you must stop the application on all nodes and confirm Synchrony process is stopped as well.

2. Run the following SQL queries in your Confluence database to delete every record associated to shared drafts.

  • Each sub-step has two queries: 1) to count the number of records that will be deleted; and 2) to actually delete them.
  • If you confirm there's no record to be deleted, there's no meaning on running the delete statement.
  • (info) The queries below were tested on MySQL and on PostgreSQL. You may need to pair with your DBA to port them to your database engine if using anything different than that.


------- Make child of drafts orphan pages -------
--- SQL query to count affected records ---
select count(c.CONTENTID) from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.CONTENT_STATUS='draft';

--- SQL query to update affected records ---
update CONTENT set PARENTID=null where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.CONTENT_STATUS='draft');



------- Delete notifications related to drafts -------
--- SQL query to count affected records ---
select count(*) from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are child in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are ancestors in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete links related to drafts -------
--- SQL query to count affected records ---
select count(*) from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete labels relation to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete users relation to drafts -------
--- SQL query to count affected records ---
select count(*) from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');


--- !! THIS TABLE DOES NOT EXIST AFTER CONFLUENCE 7.X !! ---
------- Delete external links related to drafts -------
--- SQL query to count affected records ---
select count(*) from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete likes related to drafts -------
--- SQL query to count affected records ---
select count(*) from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachment properties related to drafts -------
--- SQL query to count affected records ---
select count(PROPERTYID) from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to delete affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to count affected records ---
SELECT count(CONTENTID) from LINKS where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to delete affected records ---
delete from LINKS where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of attachments related to drafts -------
--- SQL query to count affected records ---
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
--- SQL query to update affected records ---
delete from BODYCONTENT where BODYCONTENTID in (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 


------- Delete image properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete previous versions of attachments related to drafts -------
--- SQL query to count affected records ---
select count(a.CONTENTID) from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachments related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete properties of comments related to drafts -------
--- SQL query to count affected records ---
select count(cp.PROPERTYID) from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where PROPERTYID in (select cp.PROPERTYID from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of comments related to drafts -------
--- SQL query to count affected records ---
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from BODYCONTENT where BODYCONTENTID in (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete any other contents related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete restrictions related to drafts – first query -------
--- SQL query to count affected records ---
select count(cp.ID) from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM where ID in (select cp.ID from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete restrictions related to drafts – second query -------
--- SQL query to count affected records ---
select count(cps.ID) from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM_SET where ID in (select cps.ID from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete contents that are drafts -------
--- SQL query to count affected records ---
select count(d.CONTENTID) from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE';



------- Clear Synchrony IDs from the BANDANA table -------
--- SQL query to update affected records ---
delete from BANDANA where BANDANAKEY = 'synchrony_collaborative_editor_app_registered'
         or BANDANAKEY = 'synchrony_collaborative_editor_app_secret'
         or BANDANAKEY = 'synchrony_collaborative_editor_app_id';



------- Clear tables associated to Synchrony -------
truncate table "EVENTS";
truncate table "SECRETS";
truncate table "SNAPSHOTS";


3. Start Confluence following your standard procedure.

4. Turn Collaborative Editing OFF.

5. Rebuilding the Ancestor Table

6. Turn Collaborative Editing On.


Linked issues

Would like to see this as an enhancement in Confluence?

Please vote and comment on the following Jira Issue: CONFSERVER-35921 - Administrators should be able to see and delete drafts from all users

Last modified on Mar 7, 2025

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.