Publishing Transactions, Query Timeout

Question

I am having trouble with publishing queue, getting query timeout error. I am also trying to read publish transactions using following code but getting same error.

var filter = new PublishTransactionsFilterData();
filter.StartDate = DateTime.Today.AddSeconds(-10);
filter.EndDate = DateTime.Now;
filter.PublishTransactionState = PublishTransactionState.Success;

var publishTransactions = client.GetSystemWideListXml(filter);

If I try to get "Failed" items it works. Seems my filter is not taking start date and end date into account. Where are these publish transactions stored in the database?

update

The following is the error:

(80040E31) Query timeout expired Unable to get list of publishing queue items. SQLUtilities.OpenRecordsetByStoredProcedure SystemDAL.GetListData SystemBLST.lObjListPublishTransactions SystemBLST.IBLSystemST_GetListData ManagementInfo.GetListPublishQueue

Answer 1

You should be checking the Health of your Tridion CM Database and also be keeping a tab on size of the publishing queue. Keep Tridion Database healthy and purge publishing queue periodically.

You may want to refer the Maintenance Steps as recommended in the SDL Tridion Installation Guide document or even you want to refer this Link on SDL Live Content (requires Login):

SDL Tridion Maintenance

Answer 2

Publishing Transactions are stored in the Queue Messages table in the Tridon CM database. If it is timing out you should provide a little more context, is it timing out in the Application Layer (Core Services?) or it is timing out in the database level.

Additionally I would recommend purging the Publishing Queue, if it is Tridion 2011 or previous you will fine a Purge Queue application tool that visually will help you, if you are using Tridion 2013 you will find a Power Shell script that will help you on it as well.

Answer 3

Timeout errors on a database almost always point to lacking database maintenance, so first things I would look at is updating the database stats and rebuilding the indexes. Always make sure you do regular database maintenance or its performance will deteriorate over time.

Next I would never revert to going into the database directly because you get a timeout in the official UI of the product. Before even thinking about that you should first of all fix the problem (make sure the timeout doesn't occur anymore). Btw. fixing a timeout is NOT done by increasing it, if after proper database maintenance you still get the timeout, contact Custom Support and have them take a look at it.

Using the API is a good alternative to using the UI, and since you get the same database timeout, I think you know where to look for the actual problem now.

The 1088 rows you mention are indeed not a lot and that shouldn't cause a timeout, before version 2009 there were issues with large result sets in the Publishing Queue that required you to purge it often to keep it small, but those were not database related (more than a 1000 items were using too much memory and CPU power in the browser to display correctly and in time).

Answer 4

Try to add timeout values in the bindings of your coreservices.

receiveTimeout="00:30:00" sendTimeout="00:30:00" .

Regarding the datetime values.please find the below snippet which is working fine for me.

      XElement result =client.GetSystemWideListXml(new PublishTransactionsFilterData
               {
                   StartDate = DateTime.Now.AddDays(-1),
                   EndDate = DateTime.Now.AddDays(1)
               });