Skip to Content
0

Database sybmgmtdb / saptools uses master device

Aug 25, 2017 at 10:45 AM

153

avatar image

Hi All,

As trying to extend the space for saptools and sybmgmtdb databases (to fix the error "

'default' segment is full/has no free extents"), I ran the "alter database" commands:

  • alter database saptools on default="xxM"
  • alter database sybmgmtdb on default="xxM"

After that, in DBACOCKPIT, I saw the 2 errors "Database sybmgmtdb uses master device" and "Database saptools uses master device".

Then I checked the database details in DBACOCKPIT and noticed the master device have been added for database saptools and sybmgmtdb:

I would like to ask if anyone know the indication of these errors? Is there any harm to the system usage? How can we solve such issue?

On the server, I issued the load database with listonly='CREATE_SQL' commands and receive something like this:

CREATE DATABASE saptools
ON saptools_data_001 = '4G'
LOG ON saptools_log_001 = '512M'
WITH lob_compression = 100
, inrow_lob_length = 2000

Is there any chance that the data to saptools be written accidentally to master device?

I would be very grateful for any feedback.

Best regards,

Duy

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mark A Parsons Aug 25, 2017 at 03:07 PM
0

Background:

  • a database device can be configured with an attribute of 'default disk'; this means the device's free space becomes part of a (logical) 'default' pool of disk space
  • database devices are usually created with the 'default disk' attribute disabled; the one exception to this behavior is the 'master' device which is automatically configured with its 'default disk' attribute enabled when the master device is first created
  • a database device's 'default disk' attribute can be enabled/disabled via the sp_diskdefault stored proc
  • sp_helpdevice will display the string 'default disk' (under the 'description' column) if a device has it's 'default disk' attribute enabled
  • the create/alter database commands are typically used in conjunction with database device names; one exception to this is that the DBA can use the clause 'default' in place of a database device name; when 'default' is used, the dataserver will allocate space from any devices that make up the 'default' pool of disk space (ie, any devices with the 'default disk' attribute enabled)
  • if the create/alter database command is used in conjunction with the 'default' clause, but no free space is available in the 'default' pool of devices (eg, all space in said devices is already allocated; all devices have the 'default disk' attribute disabled), then the create/alter database command will fail

-----------------

From your description is sounds like your master device is (still) configured with its 'default disk' attribute enabled. [I'm surprised SAP doesn't disable the 'default disk' attribute after initially creating the master device.]

This would explain why your alter database/on default commands allocated space on the master device.

To remove the master device fragments from your databases I'd recommend you use the SAP-approved method for shrinking a database off of a device fragment. [while I work with ASE, I do not work with SAP applications running on ASE; and while SAP may have some notes for shrinking a database, I have no idea where/how to find said notes.]

From a strictly ASE-specific view:

  • run alter database <dbname> off master; run sp_helpdb <dbname> afterwards to verify the master device fragment has been removed from the database
  • if the alter database/off command fails to remove the master device, you may have to first allocate additional space to the database from another device (eg, alter database <dbname> ... <dbdevice_name> = <size>)

Regardless of the method used to shrink the databases off of the master device, I'd highly recommend you disable the 'default disk' attribute for the master database (sp_diskdefault 'master','defaultoff').

I'd also suggest not using the 'default' clause then running the create/alter database commands ... but rather specify actual database device names. [There's technically nothing wrong with using create/alter database/default if you understand how the default pool of disk space is managed **AND** you make sure the master device is not part of said default pool, ie, the master device's 'default disk' attribute is disabled.]

Share
10 |10000 characters needed characters left characters exceeded
Duy Le Aug 28, 2017 at 07:02 AM
0

Hi,

Thank you very much for you detailed explanation, it's very useful. Your solution (alter database <dbname> off master) also works, thank you very much for your help.

Best regards,

Duy

Share
10 |10000 characters needed characters left characters exceeded