We have a waste basket (papperskorg/recycle bin) containing more than 25,000 pages (!!), we are trying to empty it is a number of ways now, but it seems to be impossible, so we are kindly asking for advice and support in doing this...
The reason it is so large is that we have many editors and there were many references to pages in the waste basket. We finally got them to clear it up.
- The empty recycle bin button does not work (of course), it times out after the timeout specified (it is set to 300 secs now).
- Deleting pages one by one takes very long time and makes the server run slow so out editors cannot work.
- We have made an aspx-page that deletes 10 pages (plus any children) at a time, but it never succeds within 300 seconds, plus this way the edit interface runs slow too.
- Last night we tried to empty it manually with inifinte timeout from query analyzer, but after 11 something hours it still was not finished and things were running slow so we had to abort it.
This is the script we ran:
SET TRANSACTION ISOLATION LEVEL READ COMMITTEDgoBEGIN TRANSACTIONgoexec editDeleteChilds @PageID = 2, @ForceDelete = 1goCOMMIT TRANSACTIONgo
So, could someone please advice us what to do!
RegardsFredrik StolpeWeb developerUR
We have a FAQ on recycle bin issues, does it help you if you follow any of those steps?
Thanks for the answer,
The FAQ did not provide me with any new insight.
The main problem is that deleting pages runs so slow and affects the overall performance. Like I said, when we tried to empty the recycle bin we had to abort after 11 hours, now I did some more timing;
Deleting one page in the waste basket took 20 secondsDeleting one page with 2 children took 40 secondsDeleting one page with 4 children took 1 minute and 15 seconds
As you see it runs really slow. If one page in average takes 15 seconds to delete, deleting 25,000 pages will take 375,000 seconds which is almost 11 days (!!) I got the math right...
And during these deletions things for the editors as well as end users runs slow or hangs, so we cannot do it in the background, so we are not sure on how/when to do this!
The only idea we got is to shut down the site for planned maintenance, which is something we rather aviod doing, and we can't take it down for 11 days!
So I'm checking to see if you have any other suggestions. Are there any database settings we can do, or any database scripts that are more efficient etc etc...?
That should have been "...11 days (!!) IF I got the math right..."
It seems you have a slight performance issue with your database
What if you take a backup of the database. Restore this on another server, and do the deleting/cleaning there. In the meantime the site will run as normal. When the copied database is cleaned, you should restore it on the production server without having a long downperiod for maintenance.You should perhaps try to minimize the production of new content in the period while you clean the database, this way you don't have to import the newly added content after the original database is restored
Yep, after som research, this is our plan. I did a test and got the wast basket script to execute in 34 hours, so we'll do a weekend maintenance job.
I also go the advice to run sp_debugtree first, might make the data more optimized...
We have a lot of data being entered by users, discussion forums etc, so we will have to do a massive work to close those things down during the weekend... but that will do it.