Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Truncate Log on Checkpoint Not Working.

3 posts in Windows NT Last posting was on 1998-03-16 12:25:10.0Z
Rosalina Africano Posted on 1998-03-13 18:35:59.0Z
From: "Rosalina Africano" <blossom_@rocketmail.com>
Subject: Truncate Log on Checkpoint Not Working.
Message-ID: <01bd4eb7$ac725e40$e18346a2@workstation1.amsinc.com>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.sqlserver.nt
Date: Fri, 13 Mar 1998 13:35:59 -0500
Lines: 4
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5117
Article PK: 1092481

Although this option is activated on my database, my transaction log still
seems to fill up, freeze, and not truncate. Any simple answer?


John McVicker Posted on 1998-03-16 12:25:10.0Z
From: "John McVicker" <mcvicker@sybase.com>
Organization: 192.175.209.26
References: <01bd4eb7$ac725e40$e18346a2@workstation1.amsinc.com>
X-Newsreader: AspNNTP (Advent 2000, Inc.)
Subject: Re: Truncate Log on Checkpoint Not Working.
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <7Lx5PaNU9GA.520@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Date: Mon, 16 Mar 1998 07:25:10 -0500
Lines: 27
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5113
Article PK: 1092479

Rosalina,
To find out if you have a long-running transaction in
Version 11 and up, look at the row(s) in master..syslogshold.
That may show someone with a long running tran that will block
a tran log from being trimmed every minute.

It all depends on log size and how much work is being put
through. Mark's right in that you may actually be putting through
enough work by a large batch process to fill the log before you commit
and this is solved with a larger log allocation or by breaking up
the work into smaller units of work. Sometimes, in a development
server, DBAs don't create a log big enough and you end up with
occasional full logs even with truncate log on chkpt turned on.

On Fri, 13 Mar 1998 13:35:59 -0500,
in sybase.public.sqlserver.nt

Rosalina Africano <blossom_@rocketmail.com> wrote:
>Although this option is activated on my database, my transaction log still
>seems to fill up, freeze, and not truncate. Any simple answer?
>

John McVicker
Sybase Professional Services
Philadelphia, PA


Mark A. Parsons Posted on 1998-03-13 20:38:20.0Z
Message-ID: <3509993C.2EBD@compuserve.com>
Date: Fri, 13 Mar 1998 15:38:20 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Truncate Log on Checkpoint Not Working.
References: <01bd4eb7$ac725e40$e18346a2@workstation1.amsinc.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 58
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5116
Article PK: 1092480


Rosalina Africano wrote:
>
> Although this option is activated on my database, my transaction log still
> seems to fill up, freeze, and not truncate. Any simple answer?

Simple answers? <g>

1) You have a long running transaction (i.e., it's been open for a long
time) that is holding an 'open transaction' marker in the log; no matter
what you do you cannot trunc the log past the first 'open transaction'
in the log. Neither you nor the checkpoint process can get around this
situation. 2 solutions ... kill the offending process thus shutting
that 'open transaction' ... or bounce the dataserver. (If you can kill
the transaction and clear the log this is better since bouncing the
dataserver will require your entire log to be played back ... and
depending on the size of your log ... this could take awhile for your
database and thus dataserver to come back on line). Ohhhh, to find the
offending process holding an 'open transaction' ... if you're running
Sybase 11.x you can look in master..syslogshold; if you're running
pre-System 11 ... let us (the forum know) ... the method is a little
more involved (read: I have to go find my notes).

Ohhhhh, and remember ... 'open transaction' could just be one big
honkin' transaction that's generating more logging activity than your
log can handle, i.e., your log may be too small. Solution(s) here are
to a) increase space of log or b) re-write offending process to do it's
work in smaller increments thus giving the checkpoint process enough
time to trunc the log before it fills.

2) If you have an LTM (Replication component) defined for this database
... and the LTM is not moving it's trunc point ... then neither you, nor
the checkpoint process, can move past the LTM's trunc point in the log.
(The LTM trunc point acts just like an 'open transaction' in that the
log cannot be truncated past the LTM trunc point.) If this is your
situation ... is the LTM up and running? Has the stable device filled
up on your RepServer?

OK, that's a couple simple answers!! <g>

If these don't help ... can you provide the following:

1) Version of Sybase
2) output of 'exec sp_helpdb <db>'
3) If running Sybase 11.x the output of 'select * from
master..syslogshold'

--
Mark Parsons
Iron Horse, Inc.