25
Jan

Since 15.7 sp1xx there has been a great new facility to increase and decrease log segments in an ASE database but did you know that you could end up with a row in the sysusages table that you cannot get rid of?
Further research shows this to be a know issue with the use of the ‘log off’ command

” …… alter database log off automatically removes any shrunken space from the end of the database. Any space removed that is not at the end of the database always becomes a hole.”

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1570/html/sag2/CIABDCEE.htm

So, don’t use ‘log off’ if the device is not the last device allocated in the database. That means you can still use this to quickly add and remove log devices but remember to drop the extra log device before you add more segments or you’ll end up with more holes in your sysusage table.

Here is how to replicate the issue
disk init name="sales_db_dev", physname="g:\sybase_devs\sales_db_dev.dat", size="3M"
disk init name="sales_db_log1", physname="g:\sybase_devs\sales_db_log1.dat", size="6M"
disk init name="sales_db_log2", physname="g:\sybase_devs\sales_db_log2.dat", size="6M"
go
create database sales_db on sales_db_dev=3
log on sales_db_log1=3,
sales_db_log2=3, sales_db_log1=3, sales_db_log2=3
go
select * from sysusages where dbid=db_id("sales_db")
go
alter database sales_db log off sales_db_log2 --<<<<< this is where we create the 'hole' in the sysusages table
go
-----------------------------------------------------------------------------
-- issue has been recreated !!!!! --
-----------------------------------------------------------------------------
1> select * from sysusages where dbid=db_id("sales_db")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno
------ ----------- ---------- ---------- ----------- -------- ------------- -------------------------- -----------
9 3 0 1536 0 0 646 Oct 26 2015 12:36PM 36
9 4 1536 1536 0 0 1530 Oct 26 2015 12:36PM 37
9 0 3072 1536 3072 9 1530 Oct 26 2015 12:36PM -9
9 4 4608 1536 1536 0 1530 Oct 26 2015 12:36PM 37

As you can see, there is now row that has a value of 0 (zero) in the segmap column. A value of 0 (zero) means that the corresponding space will not be used for further space allocation (data, log, or user-defined objects). The error is not fatal but it does indicate that any free space on that section of the database will not be used. sp_helpdb output shows a summary of the size of the database excluding holes.

So what happens if you dump and load this database into another database? Sybase says you have to take additional care and allow for the wasted device. We have witnessed it mess up the segment mapping and result in mixed devices, making log recovery impossible. So it is good practice to remove these rows from
sysusages and not have to worry about issues with database loads in the future.
If you dump the database and then check the contents of the database with headeronly, you will see output like this. It shows an Unavailable disk fragment, which is the locally assigned device that is missing an underlying physical device.
load database sales_db from 'i:\backups\sales_db.1.dmp' with headeronly
go
segmap: 0x00000003 lstart=0 vstart=[vpgdevno=36 vpvpn=0] lsize=1536 unrsvd=638
segmap: 0x00000004 lstart=1536 vstart=[vpgdevno=37 vpvpn=0] lsize=1536 unrsvd=1522
Unavailable disk fragment: lstart=3072 lsize=1536
segmap: 0x00000004 lstart=4608 vstart=[vpgdevno=37 vpvpn=1536] lsize=1536 unrsvd=1530

How to fix this
Just deleting the row from sysusages will not work, as the logical allocation is in there somewhere and you cannot get rid of it that easy. In fact we were unable to find a way to drop the device completely, so we decided to fix it by re-allocating the device and at least getting some use out of it. Just alter the database back onto the discarded device or create a device of the same size and alter the database on to that. You can get the device size from
sysusages.size column. Just divide the value by 512.
alter database sales_db log on sales_db_log2=6
go
1> select * from sysusages where dbid=db_id("sales_db")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno
------ ----------- ---------- ---------- ----------- -------- ------------- -------------------------- -----------
9 3 0 1536 0 0 1530 Oct 26 2015 1:03PM 36
9 4 1536 1536 0 0 1530 Oct 26 2015 1:03PM 37
9 4 3072 1536 0 0 1530 Oct 26 2015 1:03PM 38
9 4 4608 1536 1536 0 1530 Oct 26 2015 1:03PM 37
9 4 6144 1536 1536 0 1530 Oct 26 2015 1:03PM 38

Additional note:
I raised a case with SAP on this to confirm they approved or our solution. They said our solution was perfectly sound
“The proposed solution does fix the hole in sysusages, solves the page continuation problem of the removed device and there won’t be any problems with the log.”
They also suggested an alternative way to fix the hole in sysusages. I have not tried this method but it seems reasonable.
– dbcc usedextents(dbname,0,1,1)
– dbcc dbrepair(dbname,remap)

0 No comments

Leave a Reply

Your email address will not be published. Required fields are marked *