log free space going negative ( < 0 )

[Jesus M. Salvo Jr.]

ASE 12.5.3 ESD #4


I was performaing a large number of updates on a userbd, at which point it ran out of space on the log device. So naturally, I got the message:

Space available in the log segment has fallen critically low in database 'aggregation'. All future modifications to this database will be suspended until the log is successfully dumped and space becomes available.
The transaction log in database aggregation is almost full. Your transaction is being suspended until space is made available in the log.

Checked the sp_helpdb output for free space on log device. Yup, it is very low.

I then canceled the operation ( ctrl-c ), which would presumably rollback the transaction.
Then, I noticed sp_helpdb free log space going negative!!!.

Here is the sp_helpdb output for the specified db:


device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------------- ----------------
aggregation_default_dev 7650.0 MB data only Apr 21 2005 10:59AM 84648
aggregation_log_dev 1024.0 MB log only Apr 21 2005 10:59AM not applicable
aggregation_index_dev 2950.0 MB data only Apr 21 2005 10:59AM 2345236
aggregation_index_dev02 8192.0 MB data only Apr 21 2005 10:59AM 2011540
aggregation_default_dev02 8192.0 MB data only Apr 21 2005 10:59AM 0
aggregation_default_dev03 11264.0 MB data only Dec 6 2005 5:29PM 7209468
aggregation_index_dev03 8192.0 MB data only Jun 6 2006 9:52AM 5805556


-----------------------------------------------------------------------------------------------------------------
log only free kbytes = -63716

(return status = 0)

[A.M.]

Post by Jesus M. Salvo Jr.
ASE 12.5.3 ESD #4
I was performaing a large number of updates on a userbd, at which point it ran
Space available in the log segment has fallen critically low in database 'aggregation'.
All future modifications to this database will be suspended until the log is successfully
dumped and space becomes available.
The transaction log in database aggregation is almost full. Your transaction is being
suspended until space is made available in the log.
Checked the sp_helpdb output for free space on log device. Yup, it is very low.
I then canceled the operation ( ctrl-c ), which would presumably rollback the transaction.
Then, I noticed sp_helpdb free log space going negative!!!.
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------------- ----------------
aggregation_default_dev 7650.0 MB data only Apr 21 2005 10:59AM 84648
aggregation_log_dev 1024.0 MB log only Apr 21 2005 10:59AM not applicable
aggregation_index_dev 2950.0 MB data only Apr 21 2005 10:59AM 2345236
aggregation_index_dev02 8192.0 MB data only Apr 21 2005 10:59AM 2011540
aggregation_default_dev02 8192.0 MB data only Apr 21 2005 10:59AM 0
aggregation_default_dev03 11264.0 MB data only Dec 6 2005 5:29PM 7209468
aggregation_index_dev03 8192.0 MB data only Jun 6 2006 9:52AM 5805556
------------------------------------------------------------------------------------------------------------
log only free kbytes = -63716

What does the master..sysusages.unreservedpgs field for that device say?

-am © MMVI

[Tommy Phillips]

Jesus M. Salvo Jr. wrote:
...

Post by Jesus M. Salvo Jr.
Then, I noticed sp_helpdb free log space going negative!!!.

This is probably the known and documented problem that the system
function curunreservedpgs() is using a fast but inaccurate method for
guessing how much free space is available.

Your log did not actually use more space than is available. It's just a
reporting bug. If I recall correctly, this problem showed up in
sp_helpdb and sp_helpsegment all the way back to version 4.0, at least.

As far as I can tell, Sybase has no intention of addressing this, since
guaranteeing an accurate result from this function would require an
expensive synchronization operation.

If you look at the code for sp_helpsegment, you will see a bit of code
that attempts to scan the log to estimate log space in a database that
has mixed data and log:

/*
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
select @used_pages = lct_admin("num_logpages", db_id())


Is there anyone on the list that can comment on the accuracy of that
method, and whether there is some variant that would show unreserved log
pages in a database without mixed segments?

[Jesus M. Salvo Jr.]

Post by Tommy Phillips
...

Post by Jesus M. Salvo Jr.
Then, I noticed sp_helpdb free log space going negative!!!.

This is probably the known and documented problem that the system
function curunreservedpgs() is using a fast but inaccurate method for
guessing how much free space is available.
Your log did not actually use more space than is available. It's just a
reporting bug. If I recall correctly, this problem showed up in
sp_helpdb and sp_helpsegment all the way back to version 4.0, at least.
As far as I can tell, Sybase has no intention of addressing this, since
guaranteeing an accurate result from this function would require an
expensive synchronization operation.
If you look at the code for sp_helpsegment, you will see a bit of code
that attempts to scan the log to estimate log space in a database that
/*
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
Is there anyone on the list that can comment on the accuracy of that
method, and whether there is some variant that would show unreserved log
pages in a database without mixed segments?

Thanks. I thought for a while that Sybase was writing to something that it
was not supposed to.

[A.M.]

Post by Tommy Phillips
If you look at the code for sp_helpsegment, you will see a bit of code
that attempts to scan the log to estimate log space in a database that
/*
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
Is there anyone on the list that can comment on the accuracy of that
method, and whether there is some variant that would show unreserved log
pages in a database without mixed segments?

I can't comment on the accuracy of lct_admin() but I
can think of a few other ways of determining log size.

Dump tran comes to mind first (hint: don't truncate the log
while doing the dump).

sp_spaceused syslogs (not really that good but its worth
a shot).

-am © MMVI