Unable to get list of publishing queue items. Timeout expired

Question

I am on Tridion 2009 SP1. At one point the ability to view the publishing queue for all users (i.e. not filters) just stopped working. In the CM GUI a timeout error is received:

(80040E31) Timeout expired
Unable to get list of publishing queue items.

SQLUtilities.OpenRecordsetByStoredProcedure
SystemDAL.GetListData
SystemBLST.lObjListPublishTransactions
SystemBLST.IBLSystemST_GetListData
ManagementInfo.GetListPublishQueue
Request.GetList

So I tried using the Publication Queue Manager Powertool to clean up the queue, but that just throws a 500 error, which is consistent with having too many items in the queue.

Then I tried purging the queue using the Tridion Purge Tool, but it crunches for a few seconds and returns the same error:

14-May-2012 21:10:12 Log cleared.
14-May-2012 21:10:12 Purge action started at 14-May-2012 21:10:12
14-May-2012 21:10:12 Keeping the last 5 versions.
14-May-2012 21:10:12 Recursive mode: False
14-May-2012 21:11:12 FAILED: <?xml version="1.0"?>
<tcm:Error xmlns:tcm="http://www.tridion.com/ContentManager/5.0" ErrorCode="80040E31" Category="7" Source="Kernel" Severity="1">
    <tcm:Line ErrorCode="80040E31" Cause="false" MessageID="4613"><![CDATA[Unable to get list of publishing queue items.]]>
        <tcm:Token>RESID_4485</tcm:Token>
        <tcm:Token>RESID_15821</tcm:Token>
    </tcm:Line>
    <tcm:Line ErrorCode="80040E31" Cause="true">
        <![CDATA[Timeout expired]]>
    </tcm:Line>
    <tcm:Details>
        <tcm:CallStack>
            <tcm:Location>SQLUtilities.OpenRecordsetByStoredProcedure</tcm:Location>
            <tcm:Location>SystemDAL.GetListData</tcm:Location>               
            <tcm:Location>SystemBLST.lObjListPublishTransactions</tcm:Location>
            <tcm:Location>SystemBLST.IBLSystemST_GetListData</tcm:Location>
            <tcm:Location>ManagementInfo.GetListPublishQueue</tcm:Location>
        </tcm:CallStack>
     </tcm:Details>
</tcm:Error>

The event logs all show the exact same error. Oh, and yes, I have tried to restart COM+, Publisher and Transport services.

So it appears that the publishing queue is in a non-accessible state. Would you please suggest what the cause could be or my next step?

Astounding

When you filter the list you do get it properly? – Maurine

On most of the users - yes. However, when I filter on myself (the guy that screwed up the queue by publishing a million items via a batch job) - it also times out. – Astounding

DB Maintenance (or lack thereof) is typically to blame for this type of errors. – Conflux

Answer 1

There are a number of things you can try;

In code:

1. Reduce the data set to maybe specific time periods (per week, per month)
2. select specific stats types (failed, success etc) one by one

On the infrastructure:

1. I am not sure what you are trying to do, but if you are just removing transactions, maybe just use the purge tool (but then as you are coding I am assuming it is not clever enough for your use case)
2. Use the purge tool to remove older transactions that are irrelevant to your usecase
3. Ensure the database is fully optimized
4. (as mentioned) increase the time outs in the Tridion Configuration snap-in
5. Ensure you have the latest patches for your version of Tridion (there were a number of changes in performance of the queues for both 2009 GA and SP1
6. In general ensure the hardware is performing

Gigantism

+1 for "Use the purge tool to remove older transactions", although I suspect it will hit the same timeout. – Deckle

Hi Nickoli. Do you know which item, or which combination of items, from those listed fixed the problem please? We are having the same issue and I suspect that optimising the database and purging the publish queue will help this, but would appreciate your feedback please. Thanks, – Ortrude

Answer 2

Maybe you can query the publish transactions table to get a list of the tcm uri's of all your transactions, move this in a custom database and use the TOM.NET API / Core Service to open each transaction individually and depending on the state delete it using the API.

This way you can remove your transactions in a controlled manner without working on the database directly.

Maurine

Thanks Arjen. In Tridion 2009 the Core Service did not exist. Using the TOM COM+ API is an option that I tried based on your suggestion. The simple program looped through each Tridion user [foreach(User u in tdse.GetUsers()], got their transactions list and deleted the transactions one-by-one. However, when trying to retrieve the list for any users that had way too many items, the GetListPublishTransactions(queueFilter) function fails with a timeout. This leads to the SQL Server query timeout setting which I will try once I get a hold of the DBA. – Astounding

How about just getting the list of TCM URIs for the publish transactions by a direct database query? Then you can still delete them through the API (and thus not get support problems on the DB). – Unpen

Answer 3

What are your timeout settings in for SQLServer and Internet Information Server? if they are at the stock defaults (can't remember off hand what they are) it might be worth trying to increase them.

If it's still failing, perhaps put a trace on the database to see why it is taking so long.

Steamroller

Answer 4

In addition to all the good points listed here, Did you optimize the Database?. You should plan update the DB stats on regular basis and also reindex. Check with your DBA to have the maintenance plans scheduled.

Quickly updating the stats on CM DB (MSSQL: sp_updatestats) in addition to cleaning/purging your transacations on regular basis will help the performance of the GUI in general.

You can check the Tridion maintenance docs here

Refreshing

Answer 5

I think, this might be causing due to 'N' number of In-Progress items lying on the publishing queue.

Don't try to remove all items at onces.

Better remove queue items in this order:-

1. Failed
2. In Progress

Addition to this, just now i saw one Hotfix.

Hotfix: CM_2009.1.74381

Have a look at this.

Sedberry

Answer 6

Restore a backup of the CM database from before you dumped a gazillion items in the queue. Not pretty, but it might get you there.

Otherwise, talk to Tridion support about what database scripts they may be willing to sanction to solve this.

Deckle