Project Server SQL querying the queue

I recently found myself in the situation where the Project Server queue service was taking up almost all of a server’s CPU. Unfortunately due to the high demands of the queue jobs, the administration page was unavailable with a connection timeout error.

One way to try and access the administration page again might have been to stop or restart the queue service or the entire server itself, but what if unpredictable behaviour had disrupted those jobs? Instead, by carefully looking at the Project Server databases we can see the draft project database stores a lot of information about the queue in tables and views starting with MSP_QUEUE.

The following query gives a summary of jobs in the queue:

01 SELECT
02   QPG.GRP_QUEUE_ID,
03   QPG.CREATED_DATE,
04   QPG.READY_FOR_PROCESSING_DATE,
05   QPM.MSG_QUEUE_BODY_TYPE,
06   QPG.GRP_QUEUE_MESSAGE_TYPE,
07   QPG.GRP_QUEUE_STATE,
08   QPG.PROCESSING_DATE,
09   QPG.PERCENT_COMPLETE,
10   QPG.COMPLETED_DATE,
11   QPG.GRP_QUEUE_ERROR_INFO,
12   QPG.GRP_QUEUE_PRIORITY,
13   QPG.LAST_MSG_ID,
14   QPG.LAST_ADMIN_ACTION
15 FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
16 INNER JOIN dbo.MSP_QUEUE_PROJECT_MESSAGE QPM (NOLOCK)
17   ON QPM.GRP_UID = QPG.GRP_UID
18 ORDER BY GRP_QUEUE_STATE DESC, GRP_QUEUE_PRIORITY, QPG.GRP_QUEUE_ID

This query gives how many jobs are remaining to run:

1 SELECT COUNT(*)
2 FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
3 WHERE QPG.PROCESSING_DATE IS NULL

This query gives how many jobs of each priority are remaining to run:

1 SELECT QPG.GRP_QUEUE_PRIORITY, COUNT(*) AS [COUNT]
2 FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
3 WHERE QPG.PROCESSING_DATE IS NULL
4 GROUP BY QPG.GRP_QUEUE_PRIORITY

There are also an interesting statistics table, although I haven’t yet found how to get a text value for GRP_QUEUE_MESSAGE_TYPE:

1 SELECT QPS.*
2 FROM dbo.MSP_QUEUE_PROJECT_STATS QPS (NOLOCK)

Ideally you would never want to execute raw SQL statements against the database, but as with any computer system, unpredictable things can happen. These queries might help with some troubleshooting.

Advertisements