Hi,

I have a stored procedure that has 2 cursors. The first cursor updates
roughly 72, 000 records. The second one should update roughly 750,000
records before it finishes.

we have a counter in each of these cursors and we have a commit at 100
records and then we reset the counter. The commit statement should
flush the log segment (aka transaction log) and create space,

but even though the commit happens (transaction state value =1) , the
log never flushes and eventually runs out of space.

Has anyone ever encountered this problem? I am not sure why this
problem is occurring. There is no special behavior of commits inside a
SP or a cursor as far as I know of.

The command "dump transaction <<database_name>> with truncate_only"
works but that is a DBA command and that is not available to the ID
that will launch this SP, so that is ruled out.

Any help will be highly appreciated.

Thanks,
Shilpa

shilpa.pradhan@gmail.com wrote:
> Hi,
>
> I have a stored procedure that has 2 cursors. The first cursor updates
> roughly 72, 000 records. The second one should update roughly 750,000
> records before it finishes.
>
> we have a counter in each of these cursors and we have a commit at 100
> records and then we reset the counter. The commit statement should
> flush the log segment (aka transaction log) and create space,
>
> but even though the commit happens (transaction state value =1) , the
> log never flushes and eventually runs out of space.
>
> Has anyone ever encountered this problem? I am not sure why this
> problem is occurring. There is no special behavior of commits inside a
> SP or a cursor as far as I know of.
>
> The command "dump transaction <<database_name>> with truncate_only"
> works but that is a DBA command and that is not available to the ID
> that will launch this SP, so that is ruled out.
>
> Any help will be highly appreciated.
>
> Thanks,
> Shilpa

A COMMIT by itself does not cause the log to be flushed. It allows
the records for that transaction to be flushable (if there are no other
open transactions that preceed it in the log). However, a DUMP TRAN
command must be run, or the "truncate log on ckpt" database option must
be set in order for the log to be truncated.

So is the "truncate log on ckpt" option set for this database?
Are there any threshold actions defined on the log segment (that might
issue a DUMP TRAN command)?

What does master..syslogshold say is the oldest open transaction for
this database?
-bret

Thanks bret

Thanks bret,

I will check these out a post a reply