Oh, you're trying to truncate the log, not truncate that one table. Sorry for my confusion.
Gary Tully's comments on https://issues.apache.org/jira/browse/AMQ-2520 show a change you could try in activemq.xml that might allow the transaction to not stay open. You'll need to apply it to both the master and any slave brokers. And I'm not sure it'll eliminate the open transaction, because I think your master will still have an ongoing SELECT FOR UPDATE, but it's worth trying. If that doesn't work, you're not going to be able to complete that transaction while any ActiveMQ broker is up, so you'll need to take an outage to do what you want. Finally, you might be able to modify activemq.xml to use a database lease locker instead of a database locker. If it works, you might want to suggest that Blackboard make that part of their configuration. Tim On Sep 8, 2016 6:47 AM, "alnaimi.ay...@gmail.com" <alnaimi.ay...@gmail.com> wrote: > Thanks for clarifying this transaction.I am trying to truncate it because > it is growing and consuming the space in the log drive and once the drive > is full no more transactions could be made against the database. > On Thu, Sep 8, 2016 at 3:42 PM Tim Bain [via ActiveMQ] < > ml-node+s2283324n4716304...@n4.nabble.com> wrote: > > > Ah, OK. That transaction is related to the Database Locker (see > > http://activemq.apache.org/pluggable-storage-lockers.html), which > ensures > > that the master broker stays the master as long as it's still alive, and > > it's expected to be long-running (and there won't be a way to force it to > > complete while the broker is up. But there should be all of one row in > > the > > table; why are you trying to truncate it? > > > > On Sep 8, 2016 3:01 AM, "[hidden email] > > <http:///user/SendEmail.jtp?type=node&node=4716304&i=0>" <[hidden email] > > <http:///user/SendEmail.jtp?type=node&node=4716304&i=1>> > > wrote: > > > > > Thankx Tim, > > > Actually I can capture what is running against the database in > different > > > time and every time it shows me this: > > > > > > session_id > > > > > > Login Name > > > > > > Database > > > > > > Begin Time > > > > > > Log Records > > > > > > Log Bytes Used > > > > > > Log Bytes Rsvd > > > > > > statement_text > > > > > > Last T-SQL Text > > > > > > 65 > > > > > > db_user > > > > > > dbname > > > > > > 51:05.5 > > > > > > 33097 > > > > > > 4104092 > > > > > > 7142883 > > > > > > NULL > > > > > > (@p1 bigint)UPDATE ACTIVEMQ_LOCK SET TIME = @p1 WHERE ID = 1 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Wed, Sep 7, 2016 at 8:41 AM, Tim Bain [via ActiveMQ] < > > > [hidden email] <http:///user/SendEmail.jtp? > type=node&node=4716304&i=2>> > > wrote: > > > > > > > Nothing says that the open transaction is the same transaction at > time > > A > > > > and time B; I've been repeatedly unable to truncate a table in Oracle > > (in > > > > an application unrelated to ActiveMQ, but it illustrates the point) > > > > because > > > > there were lots and lots of transactions per second against the > table. > > > As > > > > such, your conclusion/assumption that there's a single open > > transaction > > > > that remains open for a long period of time may be inaccurate. (Or > it > > > may > > > > not, but unless you've got something concrete to prove that the same > > > > transaction is alive between the two points in time, I'd assume > that's > > > not > > > > it.) In either case, I'm not aware of a way to force a running > broker > > to > > > > commit any particular database connection. > > > > > > > > I'd suggest you contact Blackboard for advice on how to manage their > > > > application; they might have a way to suspend the system to allow the > > > > broker's database to be cleaned up. > > > > > > > > Tim > > > > > > > > On Tue, Sep 6, 2016 at 2:08 AM, [hidden email] > > > > <http:///user/SendEmail.jtp?type=node&node=4716226&i=0> < > > > > [hidden email] <http:///user/SendEmail.jtp? > type=node&node=4716226&i=1>> > > > > > > wrote: > > > > > > > > > Hi ,we have Blackboard application which use ActiveMQ, it is > causing > > > the > > > > > database to grew huge in size , we can't truncate because there is > > > > always > > > > > active transaction. transaction Logs never get cleared out after > > taking > > > > > backup due to an open transaction. , s there any way to force AMQ > > to > > > > > commit > > > > > the transaction on a schedule? or what else could be preventing the > > > > commit > > > > > from happening . > > > > > > > > > > > > > > > > > > > > -- > > > > > View this message in context: http://activemq.2283324.n4. > > > > > nabble.com/ActiveMQ-is-causing-the-database-log-to- > > > > > grow-huge-tp4716176.html > > > > > Sent from the ActiveMQ - User mailing list archive at Nabble.com. > > > > > > > > > > > > ------------------------------ > > > > If you reply to this email, your message will be added to the > > discussion > > > > below: > > > > http://activemq.2283324.n4.nabble.com/ActiveMQ-is- > > > > causing-the-database-log-to-grow-huge-tp4716176p4716226.html > > > > To unsubscribe from ActiveMQ is causing the database log to grow > huge, > > > click > > > > here > > > > <http://activemq.2283324.n4.nabble.com/template/ > NamlServlet.jtp?macro= > > > unsubscribe_by_code&node=4716176&code=YWxuYWltaS5heXNoYUBnbWFpbC5jb2 > > > 18NDcxNjE3NnwtMTQ2MTk0MTYwMg==> > > > > . > > > > NAML > > > > <http://activemq.2283324.n4.nabble.com/template/ > > > NamlServlet.jtp?macro=macro_viewer&id=instant_html% > > > 21nabble%3Aemail.naml&base=nabble.naml.namespaces. > > > BasicNamespace-nabble.view.web.template.NabbleNamespace- > > > nabble.view.web.template.NodeNamespace&breadcrumbs= > > > notify_subscribers%21nabble%3Aemail.naml-instant_emails% > > > 21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > > > > > > > > > > > > > > > > > > > -- > > > View this message in context: http://activemq.2283324.n4. > > > nabble.com/ActiveMQ-is-causing-the-database-log-to- > > > grow-huge-tp4716176p4716293.html > > > Sent from the ActiveMQ - User mailing list archive at Nabble.com. > > If you reply to this email, your message will be added to the discussion > > below: > > > > http://activemq.2283324.n4.nabble.com/ActiveMQ-is- > causing-the-database-log-to-grow-huge-tp4716176p4716304.html > > To unsubscribe from ActiveMQ is causing the database log to grow huge, > click > > here > > <http://activemq.2283324.n4.nabble.com/template/NamlServlet.jtp?macro= > unsubscribe_by_code&node=4716176&code=YWxuYWltaS5heXNoYUBnbWFpbC5jb2 > 18NDcxNjE3NnwtMTQ2MTk0MTYwMg==> > > . > > NAML > > <http://activemq.2283324.n4.nabble.com/template/ > NamlServlet.jtp?macro=macro_viewer&id=instant_html% > 21nabble%3Aemail.naml&base=nabble.naml.namespaces. > BasicNamespace-nabble.view.web.template.NabbleNamespace- > nabble.view.web.template.NodeNamespace&breadcrumbs= > notify_subscribers%21nabble%3Aemail.naml-instant_emails% > 21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > > > > > > > -- > View this message in context: http://activemq.2283324.n4. > nabble.com/ActiveMQ-is-causing-the-database-log-to- > grow-huge-tp4716176p4716305.html > Sent from the ActiveMQ - User mailing list archive at Nabble.com.
