How to remove the full history of a page using SQL
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
In some cases, you may want to delete the whole history of a page. Feature request (CONFSERVER-27701 - Ability to administer number of page versions for a given space or page) for more options when administering a page's history has been implemented in Confluence 7.16.0.
Resolution
If you are unsure how to deal with the database, contact your DBA. Make sure to have the database backed up completely before going further. These SQL commands were tested in some environments and they worked as intended, however it might not work in specific cases and newer versions of confluence as new constraints as changes may be done to confluence database structure. As such, a database backup is mandatory in case something goes wrong and you need to revert.
Please note this queries will only work when:
- The title of the page has not changed across versions.
- There is no other page with the same title in your Confluence instance.
The process to remove a page's history is
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.
- Shutdown Confluence.
- Backup your database in case you need to restore the data.
Run the following SQL queries on your Confluence database:
DELETE FROM extrnlnks WHERE contentid in (SELECT contentid FROM content WHERE title = 'Page Title' AND prevver IS NOT NULL); DELETE FROM bodycontent WHERE contentid in (SELECT contentid FROM content WHERE title = 'Page Title' AND prevver IS NOT NULL); DELETE FROM contentproperties where contentid in (SELECT contentid FROM content WHERE title = 'Remove my History' AND prevver IS NOT NULL); DELETE FROM usercontent_relation where targetcontentid in (SELECT contentid FROM content WHERE title = 'Remove my History' AND prevver IS NOT NULL); DELETE FROM content WHERE title = 'Page Title' AND prevver IS NOT NULL; UPDATE content SET version = 1 WHERE title = 'Page Title';Start Confluence.
- Rebuild the search index.
- Go to Confluence Admin > Cache Statistics and click on Flush all at the bottom of the page.