The Surly Admin

Purge List Items in SharePoint 2010

In August 2012 my company migrated to SharePoint 2010, and immediately ran into a problem. SharePoint 2010 has a limit on lists of 5000, and this is a pretty hard limit–it won’t even show the list items to the user if you’re over the threshold. Now, you could just go in and change the threshold to a much higher number but honestly, that completely defeats the whole purpose of the threshold which is there to improve list performance. Ideally, you want to delete items in the list until you’re under the threshold.  Here’s how I did it.

Not too hard to solve this problem, just create a view based on a date field in the list and put a filter in of [Today]-xx where xx is the number if days you want to keep. Select all and delete! Of course, nothing is ever that easy and I immediately ran into a problem. When selecting you can only select 100 items at a time, which is not too bad when you have 700 records to remove but pretty daunting when you have 14,000 (which I did). PowerShell to the rescue, again!

I first started by adapting this code I found here. The original code is a bit rough but I got it working after a bit. Started running it on about 450 items and it was running SLOW! Like after 5 minutes I stopped the script and discovered it had removed about 100 items. That 14,000 item list delete was not looking too good! But, while looking around I did stumble across another scriptblock that just might do the trick. Had to do some searching on how to do caml queries, which wasn’t too bad and a ton of testing to make sure I didn’t screw up too badly and I got it working. One thing I had run across on the various searches I had made was keeping these batches somewhat small for performance reason’s, so I limited the query to 1000 records and then set a loop to query the next 1000 and the next until all of the records were removed.

But I really wanted to be able to make it friendly and very usable, so that meant making a function out of it. That way you can pull the function code out of it and use it in other scripts if you want or pretty much do whatever you want to it. I then wrapped some logging functionality around it so you knew what it was doing and then email the results so you can see what it’s done.

And with that, I’d like to introduce PurgeListByDate.ps1

Everything is run within the script but you can easily pull the function code out and add it to you PowerShell profile or put it in another script. The function itself takes 5 required parameters:

1. spWeb: the full URL to your SharePoint root site. IE http://sharepoint
2. spSite: the path to the site where the list is loctaed. IE “/” for the root site or “/itdept”, etc.
3. spList: full name of the list you want to purge. IE “Backup Log”
4. spField: name of the field you want to use. This field must be a DateTime type field. IE “Backup On”
5. spKeep: number of days you want to keep. IE 90. This would mean the function will delete any list item where the spField (“Backup On”) is 91 days or older.

PurgeList -spWeb “http://sharepointsite” -spSite “/itdept” -spList “Backup Log” -spField “Backup On” -spKeep 90

August 7, 2012 - Posted by Martin9700 | PowerShell | list, purge, purge list, Sharepoint, Sharepoint 2010, Technical