Lets Learn

Opinion Matters

Posts Tagged ‘recyclebin’

Purging/Deleting a Huge/Large SharePoint List

Posted by Ankush on May 28, 2011

Recently I worked on a case where customer was trying to delete a huge list (it could be a simple list or workflow history list) and was looking for a custom solution to do it.

In case if it is a workflow history list then you can create a new list and set this as a history list and then you can try to delete the list items.

I started working on this and found a very good sample is already available on the web:

http://code.msdn.microsoft.com/CleanWorkflowHistory

But if you look at the code, it gets the ListItem and then it reads the List Item id so that it can create a batch processing to delete the item.

Everything is fine but still the process was slow…I reserached on this and suggested few points which I wanted to highlight here:

1. When you delete the List Items from List, it goes to first recycle bin and then it goes to the second one. So you may want to turn this off when deleting the items. Sometimes it is not practle to delete the items..so think about the overhead.. ..delete the item from a list, then 1st recycle bin and then 2nd recycle bin.

2. Delete Items in batch. instead of deleting them as a big list, delete them in chunk. (for ex: start with 2000 items)

3. This is something not recommended but can be tried in one-off case. Basically you can read List Item ID from the database instead of reading it using Object Model.

=========

============
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using System.Data.SqlClient;

namespace PurgeList
{
class Program
{
private static int iteration = 5;
private static int Count = 2000;

static void Main(string[] args)
{
if (args.Length != 1)
{
Console.WriteLine(“first argument: No of iteration”);
Console.WriteLine(“YourprgramName.exe 5”);
Console.WriteLine(“******Program terminated : Argument missing! Please give one argument ****”);

}
else
{
try
{
iteration = Convert.ToInt32(args[0]);

}
catch (Exception ex)
{
Console.WriteLine(“Error : Failed to convert arguments: {0}”, ex.Message);

return; // exit
}
Console.WriteLine(“Current System Time (Start) : ” + DateTime.Now.ToString());
try
{

for (int iterate = 0; iterate < iteration; iterate++)
{
int start = GetMinID();
Console.WriteLine(“Start Index: ” + start.ToString());
using (SPSite site = new SPSite(“http://anksps2010“))
{
using (SPWeb web = site.OpenWeb())
{
SPList wflist = web.Lists[“Workflow History”]; //workflow history
string wflistID = wflist.ID.ToString();

Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);

Console.WriteLine(“Building query… “);
StringBuilder batchString = new StringBuilder();
batchString.Append(“xml version=\”1.0\” encoding=\”UTF-8\”?>”);
int end = start + Count – 1;
for (int i = start; i <= end; i++)
{
batchString.Append(“<Method>”);
batchString.Append(“<SetList Scope=\”Request\”>” + wflistID + “”);
batchString.Append(“<SetVar Name=\”ID\”>” + Convert.ToString(i) + “”);
batchString.Append(“<SetVar Name=\”Cmd\”>Delete”);
batchString.Append(“</Method>”);

}

batchString.Append(“</Batch>”);

//Console.WriteLine(batchString.ToString());

try
{
web.AllowUnsafeUpdates = true;
Console.WriteLine(“Executing query… “);
Console.WriteLine(“Batch Execution (Start) : ” + DateTime.Now.ToString());
string result = web.ProcessBatchData(batchString.ToString());
//Console.WriteLine(result);
web.AllowUnsafeUpdates = false;

Console.WriteLine(“Batch Execution (END) : ” + DateTime.Now.ToString());

}
catch (Exception ex)
{
Console.WriteLine(“Process batch error : ” + ex.Message);

}
Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);
using (SPSite site1 = new SPSite(“http://anksps2010“))
{
using (SPWeb web1 = site1.OpenWeb())
{
Console.WriteLine(“No of item after deletion: ” + web1.Lists[“Workflow History”].ItemCount);
}
}

Console.WriteLine(“————————————————–“);

}
}
}
Console.WriteLine(“Current System Time (End) : ” + DateTime.Now.ToString());
Console.WriteLine(“———–Program Completed————“);
}
catch (Exception ex)
{
Console.WriteLine(“Error : ” + ex.Message);
Console.WriteLine(“******Program terminated due to error **** “);

}
}

}

public static int GetMinID()
{

try
{
string connString = @”Data Source=.\SHAREPOINT;Initial Catalog=WSS_Content_c20feb22657e4e2ab82f7db433f3e4c7;Integrated Security=SSPI”;

using (SqlConnection objConn = new SqlConnection(connString))
{
objConn.Open();

string sqlString = “Select min(tp_ID) as col1 from dbo.alluserdata where tp_listid = ‘6463BECE-3560-4D15-B965-B245F3203BEE'”;// workflow list id

SqlCommand cmd = new SqlCommand(sqlString, objConn);
int id = Convert.ToInt32(cmd.ExecuteScalar());
return id;

}
}
catch (Exception ex)
{
Console.WriteLine(“SQL ERROR: ” + ex.Message);
throw ex;

}
}

}
}

==================================

Try this and let me know how it goes!!!!!

Update # 1:

While working on the sample available here (http://code.msdn.microsoft.com/CleanWorkflowHistory), I hit a bug (or should I say a couple of bugs).

Bug # 1:PurgeSiteRecycleBin function again picks the item from the 1st stage (end-user) recycle bin. You need to specify ItemState to get the items from the second stage recycle bin. So currently, It deletes the item twice from the 1st stage recycle bin.

bug # 2: There is no need of PurgeSiteRecycleBin function at all. Basically, when you delete the item using batch processing, it goes into 1st stage re-cycle bin. But when you delete the item from re-cycle bin, it updates the content database so it will land in 2nd stage re-cycle bin. So unless, you want to empty the 2nd stage recycle bin,you can safely remove the code.

 

Posted in SharePoint 2010, Sp 2007 | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: