Recently I came across a post (and a fellow Tridion blogger, keep up the good work!) which answered the question “Why won’t this publication delete?“. It describes a tool (source code included) which helps you find the pages which you need to unpublish before you can delete the publication. It mentions two prerequisites which need to be met before you can delete a publication:
- No pages in the publication are published
- There are dependencies on the publication, child publications etc.
I would like to add a third prerequisite:
- Check the publishing queue for publish transactions which belong to the publication to be deleted which have the status “in progress”. In some instances publication transactions never leave this state and they will prevent you from deleting the publication.
In addition I would like to propose another way of ‘unpublishing’ all items in a publication (pages, components, MM components etc) by running the following query on the CM database (back-up the DB first) :
SET STATE = 0
WHERE (PUBLICATION_ID = ##)
This will not remove the records from the Tridion broker DB or any files from the webserver. However in many cases removing a publication from Tridion corresponds to removing a website as well and deleting the files manually will prove to be much faster than unpublishing them. I recently chose this route because my delete task comprised of no less than 12 publications containing thousands of pages and published (MM) components. In addition this activity needed to be executed throughout an OTAP environment.
Good and useful article. This is definitely something allot of customers face.
However, be careful when dispensing advice that includes using queries that update, insert, delete records in the CM database. this is strictly not supported buy Tridion customer support and so in essence shouldnt be done. 🙂
Im not saying it wont be done by customers and partners but they should be aware that any issues resulting from such operations in fact invalidate the support they are entitled to from Tridion.
In the end the best advice is to always make sure you make a full backup of your database before performing any direct change to the DB.
You are completely right. Thanks for mentioning it.
Thanks for the mention 🙂 I’ve also had (there was no other option) to run the very same SQL script in order to remove publishing information in the following situation:
1) we’ve a bunch of pages published to the target “LIVE”
2) we deleted the target “LIVE”
The database now ‘thinks’ that the pages are published, but to a target that no longer exists. Now there is no way to ‘unpublish’ except using a script similar to the one you’ve pasted above.
I’ve not tested if this has been corrected in 2009.
Nice addition thanks.
This is nice article. Would this script be applicable to 2011 sp1 version also.
I would like to run the same script for one of my publication also.
It would be good if you could also include impacts and rollback options.
Hi Albert ,
In addition to the ITEM_STATES one need to update the following tables as well using below written script :
delete from ITEM_STATES where PUBLICATION_ID = XX
delete from PUBLICATION_ITEM_ASSOCIATIONS where PUBLICATION_ID = XX
delete from PUBLISH_TRANSACTIONS where PUBLICATION_ID = XX
delete from TRUSTEE_RIGHTS where PUBLICATION_ID = XX
delete from PUBLICATION_PRIORITIES where CHILD_PUBLICATION_ID = XX
delete from PUBLICATIONS where ID = XX
tnx @Sharanjit for keeping the content up to date for more recent versions.