So you might have an MCMS site that has been kicking around for a few years... lots of content has been deleted. Site is getting sluggish. You go into Site Manager to empty the deleted items and everything you click takes 1 minute to respond. Emptying the deleted items is useless - it just crashes. The reason for this, I think, is that it retrieves XML from MCMS and when trying to manipulate the huge XML files in the XML DOM it just falls over. The DOM does have a maximum logical size of XML it can manipulate before performance degrades quickly.

What to do?

Well it's a nasty one as there is no API call to empty the deleted items. Microsoft - who I've logged support calls with about this - have taken over a year to come back with nothing. Basically, you're on your own.

Now the MCMS schema is a bit nasty and if you do anything to the MCMS database directly you may invalidate your Microsoft support. Having said that, it's the only way I could find to empty the deleted items without Site Manager.

Here is the script I wrote and used. Use it ENTIRELY at your own risk. I suggest you figure out what it's doing before you run it. Good luck!

-- properties (historical)

print 'deleting properties (historical)'

delete from NodeProperty where NodeId in
(select Id from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- resources (historical)

print 'deleting resources (historical)'

delete from NodeResource where NodeId in
(select Id from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- roles (historical)

print 'deleting roles (historical)'

delete from NodeRole where NodeId in
(select Id from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- placeholder content (historical)

print 'deleting placeholder content (historical)'

delete from NodePlaceholderContent where NodeId in
(select Id from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- placeholders (historical)

print 'deleting placeholders (historical)'

delete from NodePlaceholder where NodeId in
(select Id from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- postings (historical)

print 'deleting postings (historical)'

delete from Node where ArchiveSourceGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16)

-- properties (current)

print 'deleting properties (current)'

delete from NodeProperty where NodeId in
(select Id from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- resources (current)

print 'deleting resources (current)'

delete from NodeResource where NodeId in
(select Id from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- roles (current)

print 'deleting roles (current)'

delete from NodeRole where NodeId in
(select Id from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- placeholder content (current)

print 'deleting placeholder content (current)'

delete from NodePlaceholderContent where NodeId in
(select Id from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- placeholders (current)

print 'deleting placeholders (current)'

delete from NodePlaceholder where NodeId in
(select Id from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16))

-- postings (current)

print 'deleting postings (current)'

delete from Node where NodeGUID in
(select FollowGUID from Node where DeletedWhen is not null and Type=16)

-- pages

print 'deleting pages'

delete from Node where DeletedWhen is not null and Type=16

-- channel properties

print 'deleting channel properties'

delete from NodeProperty where NodeId in
(select Id from Node where DeletedWhen is not null and Type=4)

-- channel roles

print 'deleting channel roles'

delete from NodeRole where NodeId in
(select Id from Node where DeletedWhen is not null and Type=4)

-- channels

print 'deleting channels'

delete from Node where DeletedWhen is not null and Type=4