Change device_fragments usage

[Former Member]

Hi all,

By mistake it was extended a log device QAS_log_001 without 'log on' command, after that, the message "Caution:  You have set up this database to include space on disk 6 for both data and the transaction log.  This can make recovery impossible if that disk fails" appeared.
Now the command 'sp_helpdb QAS' show this:

device_fragments               size          usage                created                   free kbytes
------------------------------ ------------- -------------------- ------------------------- ----------------
QAS_data_001                      90112.0 MB data only            Nov 13 2013  4:07PM                    112
QAS_log_001                       10240.0 MB log only             Nov 13 2013  4:07PM       not applicable
QAS_data_001                      10000.0 MB data only            Nov 19 2013  9:54AM                      0
QAS_data_001                      10000.0 MB data only            Nov 19 2013 10:06AM                      0
QAS_data_001                      10000.0 MB data only            Jan 28 2014  2:08PM                      0
QAS_data_001                      30720.0 MB data only            Mar  1 2014 10:20AM               18008880
QAS_log_001                       10240.0 MB data only            Mar  6 2014  1:57AM               10444800
QAS_log_001                       10240.0 MB log only             Mar  6 2014  2:40AM       not applicable

My question is: device QAS_log_001 with usage 'data only' will save ERP data? if not, is it possible to change device usage 'data only' to 'log only' without affecting database consistency?

Thanks for your help!

Ladislao

[Former Member]

Thank you Mark, the issue was solved!

[tilman_model-bosch]

Dear Mr Almanza

The device 'QAS_LOG_001' does have now three 'fragments. All the fragments belong to database QAS .

However, one of the fragments is a 'default/data' segment and will be used by the database for storing system and user tables - but not the transaction log.
It is not a big problem - more a cosmetic issue. If you created the device files for 'log' on a seprate file system with high speed / mirroired disk - then some of this space is now 'wasted' to store ordinary user tables instead of the transaction log.

To correct this:
DUMP the database QAS,
DROP and RECREATE database QAS with the same layout except that the data fragment on QAS_log_001 gets created on  'QAS_data_001' and then load the dump you took earlier.

Once you dumped the database you may use the command

LOAD DATABASE QAS WITH LISTONLY = 'CREATE_SQL'

to obtain a DDL for the database which you may save and edit as needed. (assuming ASE is 15.7.0.020 or higher)

You may also use the shrink database feature which is available in newer ASE releases

! Attention: The 'Shrink database' feature is supported fo SAP only as of 15.7 SP110. 

See details in ASE online documentation.

Do take a full DUMP of the database directly after using the shrink database feature !

With kind regards

Tilman Model-Bosch