CA Tuesday Tip: Watch out for NMS_MESSAGES

Discussion created by Shawn_Moore Employee on Dec 28, 2010
Latest reply on Oct 4, 2016 by Danpego
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 12/28/2010

The NMS_MESSAGES table can grow to large sizes due to some past defects. A couple good checks to examine the table with are:

select count(1) from NMS_MESSAGE_DELIVERY where MESSAGE_ID in (select ID from NMS_MESSAGES where EXPIRATION_DATE < getdate())


select count(1) from NMS_MESSAGE_DELIVERY where message_id not in (select id from nms_messages)

If either of these queries return tens of thousands of rows then you might be able to improve performance by cleaning up these residual records.

CA Technologies Clarity Support can help walk you through some steps to optimize your maintenance of this table.