Shrinking a Database SAP ASE 16

[Mark_A_Parsons]

Hello,

 Using ASE 16 SP04 PL03 Linux,   for shrinking a 2TB database, which would be the recommended method for reducing its size(just data ,no log):

Alter Database OFF or sp_shrink  considering time , locks and db  availability (single user,ect). 

The goal is to reclaim unused space , reduce number of data devices used and recover some  disk space used at OS level(RHEL 8.x)

So is uncertain if it's really worth. 

Then  bcp out/in etc..,  would be the safest way ( considering time ) ?

Here's the layout 

name	db_size	owner	dbid	created	durability	lobcomplvl	inrowlen
tesorero	2898944.0 MB	sa	12	07-Nov-23	full	0	NULL
device_fragments	size	usage	created	free_kbytes
teso_dat1	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat2	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat3	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat4	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat5	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_log1	12288.0 MB	log only	Nov  7 2023 11:02AM	not applicable
teso_log2	12288.0 MB	log only	Nov  7 2023 11:02AM	not applicable
teso_dat6	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat7	12288.0 MB	data only	Nov  7 2023 11:02AM	128
teso_dat8	12288.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat9	24576.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat10	24576.0 MB	data only	Nov  7 2023 11:02AM	112
teso_dat11	51200.0 MB	data only	Nov  7 2023 11:02AM	3808
teso_dat12	28672.0 MB	data only	Nov  7 2023 11:02AM	1904
teso_dat13	30720.0 MB	data only	Nov  7 2023 11:02AM	560
teso_dat14	30720.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat15	51200.0 MB	data only	Nov  7 2023 11:02AM	112
teso_log3	56320.0 MB	log only	Nov  7 2023 11:02AM	not applicable
teso_log4	56320.0 MB	log only	Nov  7 2023 11:02AM	not applicable
teso_dat16	66560.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat17	102400.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat18	102400.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat19	204800.0 MB	data only	Nov  7 2023 11:02AM	0
teso_log5	153600.0 MB	log only	Nov  7 2023 11:02AM	not applicable
teso_dat20	153600.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat21	204800.0 MB	data only	Nov  7 2023 11:02AM	0
teso_dat22	204800.0 MB	data only	Nov  7 2023 11:02AM	9552
teso_dat23	512000.0 MB	data only	Nov  7 2023 11:02AM	80816784
teso_dat24	512000.0 MB	data only	Nov  7 2023 11:02AM	384889712
teso_dat25	204800.0 MB	data only	Mar  4 2026  6:06PM	198927904
No Column Name
log only free kbytes = 296624160

Thank you

Jose

[Mark_A_Parsons]

You may want to provide more details, eg:

• shrinking a 2TB database - how much space are you looking to remove from the database?
• considering time , locks and db  availability - what are your time and availability limits?
• reclaim unused space - does this include a need to defrag tables and indices?
• reduce number of data devices - are you looking to drop devices and/or combine multiple small(er) devices into fewer larger devices (eg, replace teso_dat1, teso_dat2, teso_dat3 with new_dat1)?
• recover some  disk space used at OS level - how much space are you looking to recover? do you have a lot of database devices with unused space (would you be looking at shrinking said devices)?

[jmtorres]

Thank you Mark. Let me gather more details, and I'll come back to you. For now, mainly reduce number of devices and save some disk space (as much as possible)

[bart_van_kuijk]

For a 2T database BCP  in / out does not seem feasible. You have to keep the DB transactionally quiet for all the time it takes.

Looking at the device layout, teso_dat24 and teso_dat25 are big and partially used. But teso_dat23 only has ~80G free which is not enough to absorb the data from  24 / 25 if you were aiming at removing those. In the big picture of a 2T database, 80G free space is not that much in any case for overheads / future growth. I am not sure if you are planning a larger data cleanup but on current usage, I would say only remove teso_dat25 (~10% of total size). But if you cleaning up data to a point where you can remove devices 24 & 25, that is 700G freed up which is substantial. 

For shrink database, I would recommend to only shrink whole device fragments and to remove them one by one per line of sysusages, each time only removing the last full row of sysusages to avoid leaving allocation holes in the database.

These (24 /25) are fairly large devices, so shrink is likely to be time-consuming. Most of the time in shrink is spent moving data. You can let this happen naturally first so shrink really only has to remove the allocation. You can do this by setting one or more devices not to be used anymore (segmap 0).  You can set this for multiple device, but again only then shrink them per one (last) line of sysusages.

Setting segmap to 0 can be done by direct update of sysusages, but the supported command method is sp_dropsegment, for example:

sp_dropsegment "default", tesorero, teso_dat25
go
sp_dropsegment "system", tesorero, teso_dat25
go

This will then no longer write data to device 25 and with normal activity and maintenance (reorgs) data the allocation will reduce. You can do this actively too. Identify which objects have data on now 0 device and start running reorg rebuilds to migrate data off.

ASE 16.1 PL02 finally has a command to lists object on a device (will also be in SP04 PL09):
Until that time, use the example code from KBA 3159484 

Shrinking a database is not a trivial operation. I would run it single user mode and having taken a full master and database dump of the db to be shrunk, so there is a clear restore point. Single user mode only tends to be feasible if you have cleared data from the device in advance as described above . Most people would not have the time window available to run a 'full' shrink (with data moving) in single user mode.

Bart

[sladebe]

When you say "setting one or more devices not to be used anymore (segmap 0)" that's for new allocations only. After applying this setting, the device is still "in use" because of the existing data on it (which doesn't move when the setting is applied)

[bart_van_kuijk]

Correct, but think I cover / explain that "You can let this happen naturally first so shrink really only has to remove the allocation. ... This will then no longer write data to device 25 and with normal activity and maintenance (reorgs) data allocation will reduce. You can do this actively too. Identify which objects have data on now 0 device and start running reorg rebuilds to migrate data off."

[jmtorres]

Thank you for all the help guys. Appreciated. We'll evalauate if its' worth the risk /effort

[sladebe]

When shrinking a database, you have to have as much space free as the space you're trying to free (roughly, sometimes it can be slightly less)

If you have text/image columns with out backlinks (the default for new databases), you'll need to add the backlinks using dbcc shrinkdb_setup ().   I think this might take a while for large databases.   I think dbcc shrinkdb_setup() may not be supported for datarow locked tables.

But the really killer restriction is shrinking effectively doesn't work with non-unique indexes with a lot of duplicate values (to be specific, it it'll run but will take forever).   To check for this run alter database MyDB off mydevice with check_only:

> alter database MyDB off MyDevice with check_only;
ALTER DATABASE: segment 'default' (ID 1) in database 'MyDB' does not have enough free space to receive data that must be moved.
ALTER DATABASE: index myindex1 of table mytable1 may have many duplicated keys. Consider dropping this index before the operation, and recreating it later.
ALTER DATABASE: index myindex2 of table mytable2 may have many duplicated keys. Consider dropping this index before the operation, and recreating it later.

Also, at my site, when we were shrinking really large databases, we occasionally hit problems with extents accounting for OAM pages (SAP Note 0003696618)