0

Recently we implemented Incremental(Enabled allow incremental dumps) and Log Backup(Disabled trunc Log on chkpt) for Sybase ASE(15.7 SP139) running on HP-UX Itanium server and to our great surprise, Size of Log backup is increasing from past 3 days continuously. Even after Full/Cumulative backup, size of the Log file kept on increasing at disk and even size at database is not clearing.

Checked logsize at different interval by running command sp_helpsegment logsegment within database and noticed that used_pages is constantly increasing and free_pages is on decline.

We even checked dbcc gettrunc to get details however it was of no use. Below is size of files Log Size

We are using 2 stripe for database dump and compress 5 for compressing size on disk while taking dump.

Replication is running on this database and is working fine.

Appreciate if I can get expert advise on this issue.

Improve this question
3
  • Looks like you're doing a cumulative every hour
    – Philᵀᴹ
    Jul 29, 2018 at 10:15
  • Cumulative backup is scheduled to run once in 4 hour whereas Log backup is scheduled to run every hour. Full backup is running only once a day. Apologies for not mentioning this in my question.
    – Learning_DBAdmin
    Jul 29, 2018 at 10:48
  • 1
    make sure you don't have a long running (aka open) transaction in the database; start by looking in master..syslogshold; common causes a) someone issued a begin tran but no timely commit/rollback and session is still active, b) replication trunc pt is not moving (either repagent is having problems or db was dumped from an actively replicated db and someone forgot to clear the trunc pt after loading into this db) and c) a broken distributed txn (similar to begin tran and no matching commit/rollback)
    – markp-fuso
    Jul 29, 2018 at 11:51

1 Answer 1

Reset to default
0

Thank you markp, this was spot-on. There was a hanging transaction from past 3 days and that was the cause of this increment in log-size.

Hanging Transaction

After investigation, we found out that there is flaw in application wherein application doesn't disconnect itself if user doesn't close them. We called user and once he closed his session, everything started smoothly and logisze came down from 9GB to 300MB.

I am posting this here as an answer, it could helps others in case they face similar issues. Thanks once again.

Improve this answer
1
  • 1
    good to hear; something to keep in mind ... if you ever have to shutdown your database with one of those long running transactions still active, upon starting up the database (or loading a dump into other db) you'll have to wait for the startup/recovery process to play forward everything in the log, then rollback all uncommitted transactions; the bigger the log, the longer it'll take to play forward; the larger the uncommitted transactions the longer it'll take to rollback; takeaway, you need to get some monitoring in place to watch for, and alert on, long running txns
    – markp-fuso
    Jul 29, 2018 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.