Lets Learn

Opinion Matters

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 8 other followers

  • Blog Stats

    • 74,660 hits
  • Ankush

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: