Sybase Ase, Log Database Extend "DBIF_RSQL_SQL_ERROR dump"

[former_member217141]

Dear Ase Gurus,

We are taking dump like DBIF_RSQL_SQL_ERROR,

Short Text

    SQL error 1105 occurred while accessing table "ALCLASTOOL".

What happened?

    Database error text: "[ASE Error SQL1105]Can't allocate space for object

     'ALCLASTOOL' in database 'HCP' because 'default' segment is full/has no free

     extents. If you ran out of space in syslogs, dump the transaction log.

     Otherwise, use ALTER DATABASE to increase the size of th".

When i check space on db,

sp_helpdb HCP

go

Returns screen shot

Not appliable mean : Not enough space for log database sure ?

Should increase log.database with this command ? And should i restart Sybase Ase and SAP ?

disk resize name ="HCP_log_002" , size="15G"

Best Regards

[former_member217141]

Dear Mark,

To increase *data* this comment also works ? disk resize name = .....

[Mark_A_Parsons]

'disk resize' only increases the size of the device.

'disk resize' does not increase the size of the database (think about what would happen if the device is allocated to multiple databases ... which databases would be extended and by how much for each database? net result is that 'disk resize' does not affect the amount of space allocated to a database).

If you don't have any free space available (to allocated to the HCP database) on any of your devices, then you have a couple up front options:

- add a new device

- extend (disk resize) a current device

Once you have some available space you can then extend the HCP database (onto the device with available space) via the 'alter database' command.

[former_member188958]

disk resize increases the available free space on the device.  You would still have to use alter database to have that space assigned to a particular database.

[former_member217141]

I have free space in related disk. Could you please tell me how to use "alter database" to increase it ? it is urgent a bit mate.

[former_member217141]

i have increase via "disk resize" commandi but same issue exist

I have free space in related disk. Could you please tell me how to use "alter database" to increase it ? it is urgent a bit mate.

[former_member188958]

alter database <dbname> on <devicename> = <size>

<size> is a quoted value of a number and a unit specifies, so might be (including quotes)  "100M" for 100 megabytes or "2G" for two gigabytes, etc.

[former_member217141]

Dear Bret,

in example;

alter database HCP on HCP_data_001.dat = 10G

mean increase 10 g ? Still HCP_data_001 is 104 gb.

[Mark_A_Parsons]

The size must be quoted when including the unit specifier, eg:

alter database HCP on HCP_data_001.dat = '10G'

'alter database' does *NOT* modify the attributes of the disk so if it's currently defined as 104GB in size then it will remain defined as 104GB in size.

If this fails, please post back with the entire output from sp_helpdevice (preferably as a *txt attachment if possible).

[former_member217141]

Dear mark,

So should i use below ?

alter database HCP on HCP_data_001.dat = 104G

Regards

[Mark_A_Parsons]

1 - post the complete output from running sp_helpdevice

2 - tell us how much space you want to extend the HCP database by, eg, 10G, 100G, something else?

[former_member217141]

Dear Mark,

I have applied these,

use master

go

disk resize name ="HCP_data_001" , size="10G"

go

alter database HCP on HCP_data_001.dat = 10G

go

and,

sp_helpdb HCP

go

[former_member182259]

you should have used:

alter database HCP on HCP_data_001 = '10G'

.....instead of

alter database HCP on HCP_data_001.dat = 10G

ALTER DATABASE takes the ASE *device* name - not the disk pathname.

[former_member217141]

Dear Jeff,

I have completed as u said(for data1-2,sapdiag,saptemp) do you see any wrong thing in this related screen shot ? Also only alter database  LOG action(HCP_log_001 and 002) returns = "

This command adds data space to disk 'HCP_log_001', which previously contained

only log.  You must specify WITH OVERRIDE to force this allocation.

[former_member217141]

Dear Mark,

HCP_data_001 size was 94 gb, after disk resize(10gb= it became 104gb . Then applied alter database. (So i wanted to increase 10 gb for Data and Log)

I have applied these,

use master

go

disk resize name ="HCP_data_001" , size="10G"

go

alter database HCP on HCP_data_001.dat = 10G

go

But LOG returns as ;

alter database HCP on HCP_log_001 = 10G

This command adds data space to disk 'HCP_log_001', which previously contained

only log.  You must specify WITH OVERRIDE to force this allocation.

and,

sp_helpdb HCP

go

[Mark_A_Parsons]

1 - I missed the '.dat' on the end of your device name so as Jeff's pointed out:

wrong: alter database HCP on HCP_data_001.dat = 10G

right: alter database HCP on HCP_data_001 = '10G'

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

2 - Unless functionality has changed recently, the wrong command ...

alter database HCP on HCP_data_001.dat = 10G

... should have generated syntax errors due to the invalid .dat extension on the device name plus the 10G (without quotes).

Was this really the command you issued?

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

3 - The output from sp_helpdb shows that not only was the HCP database extended onto HCP_data_001 (10G), it was also extended onto HCP_data_002 (10G), saptools_log_001 (1G), saptools_data_001 (1G) and saptempdb_ata_001 (1G).

Did you issue 5 different 'alter database' commands?

[former_member217141]

Dear Mark,

Dont care .dat, i didnt write in cmd. I have already removed .dat when i writing in cmd.

3 - The output from sp_helpdb shows that not only was the HCP database extended onto HCP_data_001 (10G), it was also extended onto HCP_data_002 (10G), saptools_log_001 (1G), saptools_data_001 (1G) and saptempdb_ata_001 (1G)

Yeah i have already applied 5 of them. But only LOG is the problem ;

alter database HCP on HCP_log_001 = 10G

go

This command adds data space to disk 'HCP_log_001', which previously contained

only log.  You must specify WITH OVERRIDE to force this allocation.

[former_member188958]

If you want to enlarge the log segment, you can use

alter database HCP log on HCP_log_001 = "10G"

The problem you reported originally was only a shortage of space on the data segment, though.  There may be no need to enlarge the log segment.

[former_member217141]

Still problem exist, when i check the dbacocpit, saptools seems full as screen shot

[former_member182259]

Kemal -

Please - stop and think about what you are trying to do.    ASE - much like MS SQL Server - allows MULTIPLE databases per instance.    If a database is shy on space - it will throw an error and report whether it was DATA, LOG or SYSTEM segment that ran out of space.   For some odd reason, no matter which segment is reported, it will ALWAYS tell you that if it was the LOG segment, you might need to truncate the log. 

In your case, you have MULTIPLE databases that need to be extended in size.   That means for EACH database you need to:

alter database <dbname> on <devicename> = <additional space>

.....so you needed to do:

alter database HCP on HCP_data_001 = '10G'

....which you did (after much trial and error)....

...but that only extends HCP by 10GB....it does nothing about the problem with saptools.    For saptools, I suggest that you FIRST check the size of the saptools device to something more realistic:

disk resize name ="saptools_data_001" , size="10G"

then increase the data size of saptools, ala:

alter database saptools on saptools_data_001 = '10G'

.....you might want to check your SAP installation - it seems to be extremely small - which is likely why you are constantly running out of disk space.

[former_member217141]

Dear Jeff,

The problem is i have already increased the size of saptools 1gb by 1gb, but DBACOCKPIT is showing different in earlier my screen shot. If you see that in dbacockpit, Total :2.049MB/Free: 0MB

But in this screen shot 4gb(i have increased it)

[Mark_A_Parsons]

Well, if that last image is a snapshot of the output from running 'sp_helpdb HCP' ... then it's showing that you keep adding the saptools* devices to the HCP database, ie, you're *NOT* adding the saptools* devices to the saptools database.

My guess is that you keep issuing "alter database HCP ..." while trying to increase the the size of the saptools databases.

When you should have been issuing "alter database saptools ..." in order to increase the size of the saptools database.

NOTICE the name of the database in included in the command 'alter database <dbname>' ... where <dbname> = HCP if you want to increase the size of the HCP database ... where <dbname> = saptools if you want to increase the size of the saptools database.

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

From your latest sp_helpdb output it would appear that you've added the following to the HCP database today:

HCP_data_001             = 10GB (as data)

HCP_data_002             = 10GB (as data)

saptools_log_001         =  1GB (as data)

saptools_data_001        =  1GB (as data)

saptools_tempdb_data_001 =  1GB (as data)

HCP_log_001              = 10GB (as log)

HCP_log_002              = 10GB (as log)

saptools_data_001        =  1GB (as data)

That's a total of 24GB added as data, plus 20GB added as log ... to the HCP database.

This would seem to match up with your DBA Cockpit output that shows ... for the HCP database:

- 23GB free in the data segment (ie, you've already used up 1GB of the 24GB you allocated today)

- 40GB free in the logsegment (ie, you increased the log size from 20GB to 40GB even though you likely did not have a problem with the log being filled up)

And because you haven't been using the correct command (ie, "alter database saptools ...."), the size of your saptools database has not been increased (ie, the saptools database is still full as per DBA Cockpit).

To verify what I just said, try running the following at the isql prompt ...

=====================

use master

go

-- to get device allocations for the HCP database:

exec sp_helpdb HCP

go

-- to get device allocations for the saptools database:

exec sp_help saptools

go

=====================

... and see what shows up in the output for each of the databases (HCP vs saptools).

[victoria_normand]

Hi Kemal,
This has already a lot of replies, let just summarize.

1) the "not applicable" you see on a sp_helpdb for log segment doesn't means "Not enough space for log". It is always returned for any log segment (let say that free space of transaction log cannot be estimated while transactions are running).

2) The error : "

Database error text: "[ASE Error SQL1105]Can't allocate space for object

     'ALCLASTOOL' in database 'HCP' because 'default' segment is full/has no free

     extents. If you ran out of space in syslogs, dump the transaction log.

     Otherwise, use ALTER DATABASE to increase the size of th".

"

indicated that your "data" segment (not the log segment) was fullfiled. You have been instructed about how to increase the data segment.

3) for the data segment fullfiled on database "saptools", your DBACockpit screenshot is showing that the data segment of "saptools" is full, and the output you sent from command line is about the available space on device saptools_data_001 (1gb) but the other 1Gb marked red has been added to saptempdb_data_001 that is a saptempdb database device.

As mentioned Mark an sp_helpdb would help to understand which devices are used for each databases, or a snapshot from your DBA Cockpit ( go to Database HCP -> Space -> Databases -> click on HCP (or saptools) -> tab "Devices" )

For information about free spaces on "saptools" database, you may check Note 1836607 - SYB: How to solve error 1105 in database "saptools".

Just for the record:  you can avoid such error 1105, to use the Automatic Database Extension feature.

HTH,
Best regards,

Victoria.