Sitecore EventQueue strikes again!

To set the stage, I have been working on a Proof of Concept for a client that requires us to prove that Sitecore can handle a large number of items.  Essentially, there will be 5000+ micro sites with about 40 pages each.  Each page will have around 5 unique components and 5 unique images.  Once all is said and done, the item count reached around 700,000.

After publishing these 700,000 items, and restarting Sitecore, my site did not start at all.

After investigating a bit, I tried to get the row count on the EventQueue table in the web database.

select count(1) from eventqueue

This didn't return, so I assumed that the count was rather large and it would take a long time to scan all of the rows to get the final count.

I wanted to know exactly how many row were in the EventQueue table.  On a past project, I had a similar problem and was able to hunt down a query that helped me out:

SELECT SUM (row_count)FROM sys.dm_db_partition_statsWHERE object_id=OBJECT_ID('eventqueue')AND (index_id=0 or index_id=1);

This query returned quickly and revealed that there were close to 1,500,000 rows in the EventQueue table.  WHOA!!

Clearly I would need to clear this table out because it was taking Sitecore an extremely long time to query this table.  This likely was the culprit of Sitecore not starting at all.

A simple truncate was not going to do it:

truncate table EventQueue

This again, wraps a transaction around the entire truncate operation and needs to load all of the rows into memory.  This would also take an extremely long time to return.  Surely there must be a way around this.  The answer lies in another query that essentially only deletes 1000 rows at a time.  It still takes a while to return, but it doesn't lock up the database when it is running because it's only creating transactions around 1000 rows at a time.  Here is the query:

SELECT 1WHILE @@ROWCOUNT > 0BEGINDELETE TOP (1000)FROM eventqueueEND

And finally, after clearing out the EventQueue table, sanity was restored and Sitecore was able to start.  The next time you have an extremeley large publish operation, you will likely have the same problem.  Hopefully you will be able to track down this post to help you out!