Show me the publish queue – but quick!

The Tridion publish queue, what can I say about it that wouldn’t be censored by the authorities right away? It can be horribly slow, that’s for sure. This is mainly because so much data is going across the line.

If you have access to the Tridion CM database, you can save yourself valuable time, but the results are sometimes hard to understand. Here is a trick to improve that!

First of all: the trick only works on SQL Server. If you’re using Oracle, you need to google on ‘create temporary table Oracle’ and modify the queries below.

If you’re on SQL Server and you manage to get into the Tridion CM database using SQL Server Studio, it is very easy and fast to retrieve the publish queue:

SELECT * FROM PUBLISH_TRANSACTIONS

The only problem I always have, is that I forget what the different values in the STATE column stand for. They indicate the state the transaction is in, like ‘waiting for publish’, ‘in progress’, ‘success’, etc.

The following query solves this issue by adding a State column with meaningful descriptions:

DROP TABLE IF EXISTS #publish_state_mapping;
CREATE TABLE #publish_state_mapping (
id int,
description varchar(50)
);
INSERT INTO #publish_state_mapping (id, description)
VALUES
(0, 'Scheduled for publish'), (1, 'Waiting for publish'), (2, 'In progress'), (3, 'Scheduled for deployment'), (4, 'Waiting for deployment'), (5, 'Failed'), (6, 'Success'), (7, 'Warning'), (8,  'Resolving'), (9, 'Rendering'), (10, 'Throttled'), (11, 'Ready for transport'), (12, 'Transporting'), (13, 'Deploying'), (14, 'Preparing deployment'), (15, 'Pre-committing deployment'), (16, 'Committing deployment'), (21, 'Waiting for CdEnvironment');

SELECT M.description as State, PT.*
FROM PUBLISH_TRANSACTIONS PT, #publish_state_mapping M
WHERE PT.STATE = M.id

You can also group by state. This will quickly give you an overview of the state your publish queue is in:

DROP TABLE IF EXISTS #publish_state_mapping;
CREATE TABLE #publish_state_mapping (
id int,
description varchar(50)
);
INSERT INTO #publish_state_mapping (id, description)
VALUES
(0, 'Scheduled for publish'), (1, 'Waiting for publish'), (2, 'In progress'), (3, 'Scheduled for deployment'), (4, 'Waiting for deployment'), (5, 'Failed'), (6, 'Success'), (7, 'Warning'), (8,  'Resolving'), (9, 'Rendering'), (10, 'Throttled'), (11, 'Ready for transport'), (12, 'Transporting'), (13, 'Deploying'), (14, 'Preparing deployment'), (15, 'Pre-committing deployment'), (16, 'Committing deployment'), (21, 'Waiting for CdEnvironment');

SELECT M.description as State, count(PT.ID) AS Number
FROM PUBLISH_TRANSACTIONS PT, #publish_state_mapping M
WHERE PT.STATE = M.id
group by M.description

The results look like this:

I tested this with SDL Web 8.5 and Tridion 9.0, but I think it will work on most Tridion versions.

Perhaps this will save you a few minutes of your time, some day.