cancel
Showing results for 
Search instead for 
Did you mean: 

used_pages remain in Sybase transactionlog even after a truncate

robhellemons
Explorer
0 Kudos

Hi All,

I'm currently running into an issue where the Sybase transaction log's used space is not completely free after a transaction log backup or a trunc of the log.

No matter what the used_pages remain in the syslog. The following did not help;

* dump transaction <SID> with no_log
* dump transaction <SID> with truncate_only
* checkpoint
* multiple stop/start of the database
* putting the database in truncate mode
* putting the database in normal mode and perform a full and transaction log backup

The version used is 16.0 SP03 PL08 with BW running on top. And yes, I'm well aware of the fact that truncating is not an option for SAP. This is a temporary solution during the build phase.

Anybody an idea what might be causing this?

Thanks,

Rob

0 Kudos

Can you paste an output of

sp_spaceused syslogs

from the affected database ?

robhellemons
Explorer
0 Kudos

Hi Tilman,

This results in the following;

name total_pages free_pages used_pages reserved_pages
--------------- --------------- --------------- --------------- ---------------
syslogs 1706240 992409 713804 27

Regards,

Rob

Mark_A_Parsons
Active Participant
0 Kudos

I'd want to look for any long-running, open transactions in the database that could be keeping the log from being truncated.

Another potential issue would be a replication agent that's unable to move the secondary truncation point (eg, the (Sybase)SAP Replication Server is down).

For both of the above issues you'll want to look for entries in master..syslogshold and master..systransactions.

If you find entries in either of these system tables then you'll want to track down the issue and address it before you'll be able to truncate the log.

0 Kudos

Strange

I would have assumed that the dbcc commands give by Mr Halford should have corrected anything that was wrong.

Did you you ever run

ALTER DATABASE ... LOG OFF

to reduce the log size ?

Mark_A_Parsons
Active Participant
0 Kudos

The output from sp_helpdb <SID> might be of use (eg, let us know if the log segment is sharing space with a non-log segment).

robhellemons
Explorer
0 Kudos

Hi Mark,


The output shows no mixed data and log for the transaction log;

1> sp_helpdb QBH
2> go
name db_size owner dbid created durability lobcomplvl inrowlen
status
---- ------------- ----- ---- ------------ ---------- ---------- --------
------------------------------------------------------------------------------------------------------------------------------
QBH 401496.0 MB sapsa 4 Jul 08, 2020 full 100 2000
trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on
log full, allow db suspect on rollback error, allow wide dol rows, defe
rred table allocation, page compression, allow incremental dumps, deall
ocate first text page, index compression, full logging for all

(1 row affected)

device_fragments size usage created free_kbytes
---------------- ------------- --------- ------------------- ----------------
QBH_data_001 12288.0 MB data only Jul 8 2020 9:33AM 224
QBH_log_001 7168.0 MB log only Jul 8 2020 9:33AM not applicable
QBH_data_001 10240.0 MB data only Jul 8 2020 9:45AM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 10:01AM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 10:20AM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 11:34AM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 12:04PM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 12:41PM 112
QBH_data_001 10240.0 MB data only Jul 8 2020 1:07PM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 1:38PM 3920
QBH_data_001 10240.0 MB data only Jul 8 2020 1:39PM 1584
QBH_data_001 10240.0 MB data only Jul 8 2020 2:29PM 1808
QBH_data_001 10240.0 MB data only Jul 8 2020 2:52PM 448
QBH_data_001 10240.0 MB data only Jul 8 2020 3:17PM 0
QBH_data_001 10240.0 MB data only Jul 8 2020 3:43PM 1024
QBH_data_001 10240.0 MB data only Jul 8 2020 4:08PM 0
QBH_data_001 10240.0 MB data only Jul 9 2020 9:40PM 0
QBH_data_001 10240.0 MB data only Jul 30 2020 3:18PM 0
QBH_data_001 10240.0 MB data only Jul 30 2020 5:29PM 0
QBH_data_001 10240.0 MB data only Jul 30 2020 9:39PM 0
QBH_data_001 10240.0 MB data only Jul 30 2020 11:27PM 0
QBH_data_001 10240.0 MB data only Jul 31 2020 11:43AM 0
QBH_data_001 10240.0 MB data only Jul 31 2020 12:51PM 0
QBH_data_001 10240.0 MB data only Jul 31 2020 1:43PM 0
QBH_data_001 10240.0 MB data only Jul 31 2020 2:32PM 112
QBH_log_001 3932.0 MB log only Jul 31 2020 8:10PM not applicable
QBH_data_001 10240.0 MB data only Aug 4 2020 10:27AM 0
QBH_data_001 228.0 MB data only Aug 4 2020 11:48AM 0
QBH_data_001 50000.0 MB data only Aug 5 2020 4:14PM 1568
QBH_data_001 10240.0 MB data only Aug 6 2020 1:07AM 19936
QBH_data_001 10240.0 MB data only Aug 6 2020 1:47AM 4912896
QBH_log_001 3072.0 MB log only Aug 6 2020 10:47AM not applicable
QBH_data_001 25600.0 MB data only Aug 6 2020 10:56AM 26112000
QBH_log_001 5120.0 MB log only Aug 6 2020 10:58AM not applicable
QBH_log_001 5120.0 MB log only Aug 10 2020 9:50AM not applicable
QBH_log_001 2044.0 MB log only Aug 10 2020 10:23AM not applicable
QBH_log_001 4.0 MB log only Aug 10 2020 10:32AM not applicable
QBH_log_001 196.0 MB log only Aug 10 2020 10:32AM not applicable
QBH_log_001 4.0 MB log only Aug 10 2020 10:33AM not applicable

------------------------------------------------------------------------------------------------------------------------------
log only free kbytes = 15846240

device segment
------------ ----------
QBH_data_001 default
QBH_data_001 system
QBH_log_001 logsegment
(return status = 0)

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member188958
Active Contributor

I suggest running

dbcc dbrepair(<dbname>, findstranded)

to verify there are no non-log allocations on the log segment.

Assuming there aren't, put the database in single-user mode and run

dbcc dbrepair(<dbname>, fixlogfreespace)

https://wiki.scn.sap.com/wiki/display/SYBASE/DBCC+dbrepair

cheers,
-bret

robhellemons
Explorer
0 Kudos

Hi Bret,

Thanks for your suggestion. Unfortunately this doesn't clear more pages in the log than I was already able to. Still only 15 GB free of the 26 GB in total.

Regards,

Rob

former_member188958
Active Contributor
0 Kudos

Ok. A bit more specific may help. When you do truncate the log as much as you can,

just how much space is still reported allocated to it? If you do a "select count(*) from syslogs",

how many log records does it indicate are still in the log? (it is normal that the count doesn't go to 0,

ASE isn't able to truncate the last extent of the log, so there will typically be ~10-100 log records

left after a couple of truncations in a row (truncate itself generates log records for deallocating extents that are being truncated).

As findstranded didn't find any other objects with allocations on the log segment, a possibility that remains is that there are some "orphaned" extents that are allocated to syslogs but aren't linked in as part of the current syslogs table (which is an APL lock scheme table and so consists of pages that are sequentially linked together with pointers in the page headers). Do you know if dbcc rebuild_log() has ever been run on this database? It has an option to build a new log, but leave the old one alone for investigation by support.

What I'd do to check that possibility is use dbcc usedextents - directing to output to a file as there might be a lot of it.
https://wiki.scn.sap.com/wiki/display/SYBASE/DBCC+usedextents

After truncating the log as much as you can, run

select firstpage from dbname..syspartitions where id = 8
go
set switch on 3604
go

dbcc usedextents(dbname, 1, 0)
go > myoutputfile.txt

Then look at that file. It will contain 1 or more entries that look like this:

1> dbcc usedextents(test2, 1, 0)
2> go
 OAMPG: 0   Extent ID 1288 on allocation page 1280
   Object ID is 8
   Index ID is 0
   Partition ID is 8
   Allocation bitmap: 0xff ( 1288 1289 1290 1291 1292 1293 1294 1295 )
   Dealloc    bitmap: 0x00 ( )
   Forward    bitmap: 0x00 ( )
   Reserve    bitmap: 0x00 ( )
   OAM        bitmap: 0x00 ( )
   status: 0x00 (EX_DEALL_NOSTATUS )
   Sort bit is off
   Reference bit is off
   Spacebits  bitmap: 0x00000000
        Page: 1288 (0x00)
        Page: 1289 (0x00)
        Page: 1290 (0x00)
        Page: 1291 (0x00)
        Page: 1292 (0x00)
        Page: 1293 (0x00)
        Page: 1294 (0x00)
        Page: 1295 (0x00)
    Buddy Page for extent (se_extbuddypage): 0
    Extent version (se_extentversion): 0
    Extent owner (se_ownerid): 0

If there is only one entry, then we can forget the theory of there being orphaned extents for syslogs.

But if there are more than one, we may be on to something. Look at the page number you got for syspartitions.firstpage - that page will belong to one of these extents, and is where the active log starts. The other extents presumably belong to orphaned extents, and if that is what you are seeing, come back and we will dig a little further.

hrollizo
Active Participant
0 Kudos

Hi,

tray with this commands:

dump tran <yourDB> to "/backup/Log/<yourDB>_log.dmp" with compression = "101"

checkpoint

go

sp_dboption <yourDB> 'trunc log on chkpt', 'true'

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1600/doc/html/san13...

Or follow this example with saptools:

dbcc gettrunc
go

use saptools
go
dbcc settrunc (ltm, ignore)
go
dump transaction saptools with truncate_only
go

use saptools
go
dbcc settrunc (ltm, ignore)
go

use master
go
sp_dboption "saptools","trunc log on chkpt", false
go

checkpoint
go

dump database saptools to "/backup/Data/saptools_FULL_1506202.dmp" with compression = "101"
go

dump transaction saptools with truncate_only
go

sp_dboption "saptools","trunc log on chkpt", true
go

select * from master..syslogshold
go

Regards

robhellemons
Explorer

Hi Diego,

Thanks for you response.

I already tried to set trunc log on checkpoint. With or without this option I'm not able to dump the transaction log and empty it completely.

I also tried your dbcc settrunc (ltm, ignore) suggestion. This unfortunately didn't make a difference as well.

Regards,

Rob