Sybase UPDATEs taking forever/timing out? Time to dump tran!
I’m a programmer, not a DBA, but I do have to be able to perform basic database configuration and maintenance tasks. One thing that’s come up, a couple times, over the past few years, is a transaction log getting full.
There are a couple of reasons this can happen:
- poorly written queries that run for a very long time
- applications neglecting to end transactions, when they encounter error conditions
Here are a few useful commands I’ve found that deal with transaction logs (Note: I’m using Sybase ASE 12.5.1):
check for long-running transactions (found here):
use master;
SELECT * FROM syslogshold
WHERE dbid = db_id(’MyDbName’);
transaction log space free:
SELECT CONVERT(CHAR,
(lct_admin('logsegment_freepages', 4) -
lct_admin('reserved_for_rollbacks', 4)) * 2)
transaction log space total:
SELECT SUM(size)
FROM master..sysusages
WHERE dbid = db_id('MyDbName')
AND segmap & 4 = 4 -- logsegment
dump the transaction log (clears it out, so things can get moving again!):
***WARNING*** Use this command at your OWN RISK! Please do some additional reading before executing the following statement, so that you fully understand the implications. The transaction log is there for a reason, and, from what I understand, you should not tamper with it unless you know what you’re doing. On the occasions that I’ve used this, it was a last resort. It solved my problems, with no ill effects, but it might not solve yours!
dump tran myDbName with truncate_only;
commit;
For additional information/reference, Sybase’s online books sometimes come in handy. When trying to find out how to dump transaction logs, I referred to their dump transaction page.
Another site I’ve found useful*, on numerous occasions, is Sybase 101. I found a related tip, there, for tips, there, for Dealing With Server Failure, which addresses what to do when your log fills up completely, such that you can’t even restart the server.
*Beware: I just noticed that Sybase 101 has become less useful than it used to be, because useful pages are automatically redirecting to one main start page. If they were just trying to prevent deep linking from the outside world, I could understand this behavior a little more; but it’s even happening when I click other links from their own site! Navigation works fine with javascript disabled, though.
subscribe to entries feed