on 08-10-2020 3:26 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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'
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.