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?
 
OCSDTechConnect With a Mentor Author 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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.