Removing deleted content from the SharePoint 2007 Content Database

We have some list items with workflows that went haywire causing those items to accumulate thousands of versions and take up too much space in the database.  I should have set the list version limit from unlimited to a number and changed the items to truncate the versions but instead I deleted the items and then emptied them from the recycle bin and site collection recycle bin.  The recycle bin cleanup job runs daily.  I temporarily set it to run more frequently (minutes) but the content is still there.  I can see the rows of the content still sitting in the alldocversions table albiet with a deletetransactionid assigned to it.  

What is the mechanism to flush these delete items out of the content database?  Thanks!

The renegade items total up a whopping 19 GB of unnecessary content so it will be definitely a good thing to get this cleaned up.

Thanks!
OCSDTechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlMothanaAlOmariCommented:
0
OCSDTechAuthor Commented:
I tried option 2 (disabling the second stage recycle bin) but that didn't clear the space from the alldocversions table.

Then I tried the custom code in option 3 but the SPRecycleBinItemCollection came up empty.

I can't quite do option 1 because I already deleted the site.  But I am increasing the job-recycle-bin-cleanup frequency to every 30 minutes.  Hopefully the job will find a lull this weekend where it can clean out the items without locking up and timing out.
0
OCSDTechAuthor Commented:
Turned down the indexing frequency on search to reduce the chance of contention with the job-recycle-bin-cleanup for one night and the items are still there.

Looks like I'll have to open a ticket with Microsoft.

This is the SQL I'm using to verify if the items are truely removed from the database.

SELECT
    [AllDocVersions].id
  , [AllDocVersions].DeleteTransactionId
  , count(*) as versions
  , sum(convert(bigint,[AllDocVersions].MetaInfoSize)+convert(bigint,[AllDocVersions].size)) as size
  FROM [dbo].[AllDocVersions] with (nolock)
  group by [AllDocVersions].id
  , [AllDocVersions].DeleteTransactionId
  order by sum(convert(bigint,[AllDocVersions].MetaInfoSize)+convert(bigint,[AllDocVersions].size)) desc
  compute sum(sum(convert(bigint,[AllDocVersions].MetaInfoSize)+convert(bigint,[AllDocVersions].size)))
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

OCSDTechAuthor Commented:
The ticket is still open with Microsoft.  Looks like we ran into a bug in the job-recycle-bin-cleanup.  Microsoft is working on it.
0
OCSDTechAuthor Commented:
The issue turns out to be a bug in SharePoint that does not actually delete the list items/versions when the item is deleted.  I got one-off SQL to clean the "deleted" data from my databases.  Sorry, I cannot post the SQL.  You must contact Microsoft support to get it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HairbrushCommented:
Since my recommendation to close and delete this question, the asker has provided a solution.  I am therefore proposing that the asker's solution is accepted, rather than a delete.
0
OCSDTechAuthor Commented:
The problem turned out to be a Microsoft bug.  Unfortunately, only Microsoft can provide the one-off fix for the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.