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?

Answer 1

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.

Answer 2

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

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.

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