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
September 4th, 2007 at 3:21 pm
I hate it when sites use javascript to obfuscate good static links. AllMusic pulled some similar crap way back then. They told me to hold down shift when I clicked on links. Lame. Once upon a time, I could go straight to a band’s discography via a command-line-guessed URL…
September 11th, 2007 at 9:09 am
Really? I don’t remember allmusic ever having useful URLs. I always wished they had simple URLs, like epguides.com. It’s nice that you can still go to http://www.epguides.com/ShowName, and it usually works. I remember you complaining to them when they changed things, at some point, so I thank you for helping to keep that site usable.
September 11th, 2007 at 11:09 am
Oh yea, once upon a time AllMusic had useful URLs. It might not have been as easy as /Bandname/, but they were LINKS to actual STATIC urls.
If you saw 5 albums listed, you could open all 5 links in new windows, then cycle through the tabs — how i ALWAYS surf the web, i REFUSE to sit there while a page loads.
Then they changed it so that all links are javascript, so if you open a link in a new window/tab, you just have “javascript:asdfasdfasdf()” in your addressbar, no page, so you have to do everything in one window, waiting for load-times each page refresh, i hate it, and i bitched them out.
I also bitched out www.tvtome.com when they stopped having /showname/ type URLs. then tvtome becamse tv.com and just went completely to hell. but now they’ve added back SOME showname URLs on tv.com…