How to Detect and Kill Runaway Queries in Documentum

I was cleaning up a Docbase and ran the following queries to determine how many Inbox messages were in users’ Inboxes.

select count(*) from dmi_queue_item;
==> 3,062,291

select name, count(*) from dmi_queue_item where datediff(day,date_sent, date(today))> 90 and delete_flag = FALSE group by name;
==> dm_fulltext_index_user : 2,968,508
==> dmadmin : 1,253
==> doceditor : 1

Wow!  Obviously, there were some messages left over from a failed xPlore configuration.  So, I set out to clean them up with a query like this:

delete dmi_queue_item objects where name = 'dm_fulltext_index_user' and delete_flag = FALSE and datediff(day,date_sent, date(today))> 90

However, deleting nearly 3 million objects had an impacted on the Docbase and system performance, so I decided to abort the query, break it up into manageable chunks, and run it off-hours.  The problem was the query was already out of the gate and running.  How do you abort a long-running or run away query? 

Here are a couple of ideas:

  • Using DA.  You can try to kill the Documentum session running the query.  In DA, navigate to Administration –> User Management –> Sessions and select the session running your query.  To kill it, select Tools –> Kill Session from the menu.  Hopefully, killing the Documentum session will kill the database session running the query also, but not always, and not always in a timely fashion.  Note the “Database Session ID” for use below.
  • In SQL Server.  Open the SQL Server Management Studio, and click on the Processes window shade on the Activity Monitor tab.  Find your Database Session ID, and kill it.  Here are some other hints for finding the correct database session to kill:
    • Your process is likely in a suspended state or waiting for a resource;  however, not always.
    • Sort the processes in a manner that is useful to find the processes run by your client.
    • Right-click each process and view its details.  If the details window contains the suspect query, kill that database process by clicking the Kill Process button.
  • In Oracle.  Unfortunately, I don’t have access to or as much experience with Oracle.  However, these two resources seem to describe an Oracle analog to the SQL Server steps listed above.

Hopefully, you’ll never need to resort to these drastic measures, but if you do I hope these tips are helpful. If you have others I’d love to hear about them.

Advertisements

About Scott
I have been implementing Documentum solutions since 1997. In 2005, I published a book about developing Documentum solutions for the Documentum Desktop Client (ISBN 0595339689). In 2010, I began this blog as a record of interesting and (hopefully) helpful bits of information related to Documentum, and as a creative outlet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: