dump tran creates dump file, but syslogs stays full 
Author Message
 dump tran creates dump file, but syslogs stays full

ASE 12.5
Solaris 8
Sun Ultra 10

I have a user-defined threshold that dumps the transaction log to an
OS file. I have been testing where to place the threshold by filling a
table up using a perl/DBD::Sybase script, and monitoring log usage
with both:

"dbcc checktable(syslogs)" and
"select lct_admin("logsegment_freepages", dbid)"

... with the intention of seeing much log space gets used before the
dump completes. The procedure that dumps tran fires when it is
supposed to, and the dump file gets created in the expected place.
However, after the proc runs, the output from the above two commands
stays the same, i.e. the log doesn't get emptied even though the dump
took place.

Any ideas?



Wed, 03 Nov 2004 03:20:46 GMT
 dump tran creates dump file, but syslogs stays full

Quote:

> ... with the intention of seeing much log space gets used before the
> dump completes. The procedure that dumps tran fires when it is supposed
> to, and the dump file gets created in the expected place. However, after
> the proc runs, the output from the above two commands stays the same,
> i.e. the log doesn't get emptied even though the dump took place.

If a process has a transaction open then the log can't be truncated
beyond that transaction. So if you are simulating the log filling up with
a transaction from the DBD::Sybase script (for example with AutoCommit
off) then the log won't get truncated until you close that transaction.

You can find the oldest running open transaction via
        select * from master..syslogshold

Michael
--
Michael Peppler                              Data Migrations, Inc.

http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com



Wed, 03 Nov 2004 06:25:53 GMT
 dump tran creates dump file, but syslogs stays full

Quote:

> I have a user-defined threshold that dumps the transaction log to an
> OS file. I have been testing where to place the threshold by filling a
> table up using a perl/DBD::Sybase script, and monitoring log usage
> with both:

> "dbcc checktable(syslogs)" and
> "select lct_admin("logsegment_freepages", dbid)"

        "sp_spaceused syslogs" should suffice.

Quote:
> ... with the intention of seeing much log space gets used before the
> dump completes. The procedure that dumps tran fires when it is
> supposed to, and the dump file gets created in the expected place.
> However, after the proc runs, the output from the above two commands
> stays the same, i.e. the log doesn't get emptied even though the dump
> took place.

> Any ideas?

        See Michael's comment about open transactions.
        Note that you can also have multiple user defined thresholds.

-am     ? 2002



Wed, 03 Nov 2004 10:20:03 GMT
 dump tran creates dump file, but syslogs stays full
Michael, Anthony:

Thank you for your input.

I just added $dbh->commit(); to the loop, and re-ran it, and the
threshold proc cleared the log nicely, twice during the 100,000 line
run of the perl script. I thought I had set AutoCommit to 1 but
apparently not. It's still odd that the dump fired and created the
expected file (isn't it?).

Thanks,

Callum

Quote:


> > ... with the intention of seeing much log space gets used before the
> > dump completes. The procedure that dumps tran fires when it is supposed
> > to, and the dump file gets created in the expected place. However, after
> > the proc runs, the output from the above two commands stays the same,
> > i.e. the log doesn't get emptied even though the dump took place.

> If a process has a transaction open then the log can't be truncated
> beyond that transaction. So if you are simulating the log filling up with
> a transaction from the DBD::Sybase script (for example with AutoCommit
> off) then the log won't get truncated until you close that transaction.

> You can find the oldest running open transaction via

> Michael



Sun, 07 Nov 2004 04:37:44 GMT
 dump tran creates dump file, but syslogs stays full

Quote:

> It's still odd that the dump fired and created the
> expected file (isn't it?).

        Not really. The dump works off the number of allocated log
        pages. It doesn't know about transactions, per se (apart
        from clearing completed ones - depending on its options).

-am     ? 2002



Sun, 07 Nov 2004 19:14:25 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Unable to empty syslogs with DUMP TRAN

2. Unable to empty syslogs with dump tran

3. dump tran and dump database simultaneously

4. dump db/dump tran questions

5. transaction log dump not dumping and system segment full

6. Help - DUMP TRAN WITH NO_LOG and Transaction Log Free Space

7. How to Error Trap failed DUMP TRAN ASAP!!!

8. tran dump

9. DUMP TRAN aborts script processing on error???

10. How to error trap failed DUMP TRAN ASAP!!

11. Dump Tran With NO_LOG does not work!

12. Distribution tran wont dump with no_log


 
Powered by phpBB® Forum Software