12-05-2013 01:25 AM
I occasionally find that the OMW OV_Message table locks up. When I issue sp_who2 on the database I see blocking locks caused by console sessions running selects against the database. These blocking locks can sometimes be seen for longer than two hours. When I observe that the console is no longer updating I may need to kill the offending session.
I cannot rely on my manually handling this kind of scenario. I need either a way of automatically killing sessions that block lock the message table or at the very least a SQL job that can email me if such a lock exists.
Anybody done anything similar and have developed some similar solution?
This is a mission critical implementation with a high message volume and I must have a workaround.
12-05-2013 06:13 AM
Is the OMW server on the latest patch OMW_00178?
Also you mentioned that message volume is high in the environment - is database maintenance configured to handle this by purging acknowledged messsages sooner. This may help as well.
Hope this helps and you get a better answer.
12-06-2013 01:07 AM
I have nearly 10 million ack messages but I am not permitted to reduce them. For this reason I need to be able to check for blocking locks or some other means of handling messages. I guess there may be some or other remote console setting that prevents locks at some or other expense (such as data integrity of console views).
12-09-2013 01:44 AM
The problem you are seeing could be due to a huge amount of messages that should be processed. Depending on the number of messages which qualify SQL Server might escalate the lock level to table lock. Then the table ov_ms_message is completely locked, no insert/update operation can be performed on the table.
I have seen this problem when DBMaint is running in order to delete acknowledged messages. For avoiding it, you could set the configuration item "Transaction isolation level during daily database maintenance" to the value 0 (read uncommitted):
- Configure > Server > Namespace "Database Maintenance". Select Expert mode. Set the configuration item "Transaction isolation level during daily database maintenance" to 0.
Apart from that, please follow the steps for Database Maintenance in order to reduce WMI load. I will send here a document that might be useful:
Please also check the following topics in the Online Help:
- Maintain acknowledged messages in the database
- Export acknowledged messages from the database
I hope this helps.
If you find this or any post resolves your issue, please make sure to mark it as an "Accepted as Solution".
If you liked the reply then please show this with KUDOs