SYBASE Backup Error on database/archive while working on strip, i/o read call failed

[Former Member]

Dear Gurus,

I am new to SAP SYBASE ASE.

I have SAP system installed with SYBASE 15.7 on SLES 11 Sp3.

After installation i have taken 4 to 5 successful backups in two months with below method

login with devadm

isql -Usapsa -SDEV -Ptest123

1>dump database DEV to "/backup/DEV/backups/filename.dmp"

2>go

its created 4 times successful backup with 73 GB.

Now i am getting below errors in backup

All spaces in backup directory and sybase directory are free.

Please help me and suggest the correct solution.

Best Regards,

QJ

[former_member198560]

Qazi,

For your original issue of Backup failure, Please check the following :

1) Check with taking the dump to a different directory (for example : /Sybase/DEV) as below :

isql -Usapsa -SDEV

1>dump database DEV to "/Sybase/DEV/filename.dmp"

2>go

2) If the above fails due to the same IO error , Please run the below consistency checks for the db :

1> dbcc checkdb(DEV)

2> go

1> dbcc checkalloc (DEV)

2> go

~Gaurav

[Former Member]

Hi Qazi,

1. To truncate the log check point,

use master

go

sp_dboption <SID>, 'trunc', false

go

2. To add space,

use master

go

disk resize
name ='saptools_data_001',
size='500M'

go

alter database <database name> on saptools_data_001='500M'

go

Please verify your database name with sp_helpdb command, refer the below link

Sybase: Extending the transaction log

Thanks and Regards,

Mathan

[Johan_sapbasis]

HI,

try without the .dat

johan

[Former Member]

Dear Nicolaas,

i removed .dat from the statement, now first statement disk resize is successful but second alter database is giving error.

see screenshot & suggest.

Regards

[former_member188958]

The ALTER DATABASE command syntax does not include the "size" keyword.

alter database saptools log on "saptools_log_01" = "800M"

[Former Member]

No Bret Same issue. i removed size.

[luc_vanderveurst]

remove the quotes around saptools_log_01.

Luc.

[DilipVoora]

Hi Qazi,

Check the device name from which you are trying to alter the space to the database 'saptools'.

In alter command you have to specify "saptools_log_001"=800M instead of "saptools_log_01"=800M

Regads,

Dilip Voora

[DilipVoora]

Hi Qazi,

Small change corrected below.

In alter command you have to specify saptools_log_001="800M" instead of  saptools_log_01="800M"

Regards,

Dilip Voora

[Johan_sapbasis]

Hi,

Apologies at the time I was busy with migration 40 hours without sleep so the syntax slipped me a bit.

Regards,

Johan

[Former Member]

Hi Bret,

Is it mandatory to restart? Database after disk resize and database on disk???

Because your suggested command syntax work fine.

But error is the same for backup.

Regards

[Johan_sapbasis]

Hi Qazi,

What did you see you in BS SID log for the backup database? Any errors matching time of failure in dmesg on O/S level?

i/o would usually indicate disk or resource issue at time of failure. Please upload the BS SID log file.

Kind Regards,

[Former Member]

Dear Nicolaas,

Where to find these logs??? as i told i am new to SYBASE.

Regards,

Qazi

[Former Member]

Tail DEV_BS.log iss attached.

Now suggest the solution

BR,

Qazi

[Johan_sapbasis]

Hi Qazi,

check the permissions on folder /sybase/DEV/sapdat see if you can do the following in this this directory:

cd /sybase/DEV/sapdat

touch test

rm test

df -k .

Also check:

cd /backup/DEV/backups

touch test

rm test

df -k.

for the serverlogs if your sidadm user has access command is:

dmesg

Also is there free space on /backup/DEV/backups and /sybase/DEV/sapdat?

Kind Regards,

Johan

[Johan_sapbasis]

In dmesg you can check for actual i/o errors like disks/etcs

[Former Member]

Dear

i have more than 650 space free in backup directory and 2.7 TB free in sapdata directory

chmod 777 is on i can create n delete files.

dmesg shown no io errors

Qazi

[Johan_sapbasis]

Hi,

Attach your DEV_BS.log and DEV.log and a dev_w* file newest one from your work directory.

Regards,

Johan

[DilipVoora]

Hi Qazi,

Check the free space on on /backup/DEV/backups and /sybase/DEV/sapdat. And try dumping the database/log.

Regards,

Dilip Voora

[Former Member]

Tail DEV.log

00:0016:00000:00246:2016/06/20 03:00:05.36 server  Can't allocate space for object 'DBH_SNAP_SYSLOAD' in database 'saptools' 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 the segment.

00:0006:00000:00091:2016/06/20 03:00:05.40 server  Error: 1105, Severity: 17, State: 2

00:0006:00000:00091:2016/06/20 03:00:05.40 server  Can't allocate space for object 'DBH_STG_THREADS' in database 'saptools' 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 the segment.

00:0010:00000:00069:2016/06/20 03:00:05.75 server  Error: 1105, Severity: 17, State: 2

00:0010:00000:00069:2016/06/20 03:00:05.75 server  Can't allocate space for object 'DBH_SNAP_STMTCACHE' in database 'saptools' 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 the segment.

00:0008:00000:00016:2016/06/20 03:00:09.64 server  Error: 1105, Severity: 17, State: 2

00:0008:00000:00016:2016/06/20 03:00:09.64 server  Can't allocate space for object 'DBH_ERROR_LOG' in database 'saptools' 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 the segment.

00:0014:00000:00199:2016/06/20 12:02:47.47 server  Error: 1105, Severity: 17, State: 2

00:0014:00000:00199:2016/06/20 12:02:47.47 server  Can't allocate space for object 'DBH_SNAP_TABLES' in database 'saptools' 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 the segment.

00:0009:00000:00181:2016/06/20 12:02:47.71 server  Error: 1105, Severity: 17, State: 2

00:0009:00000:00181:2016/06/20 12:02:47.71 server  Can't allocate space for object 'DBH_SNAP_INDEXES' in database 'saptools' 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 the segment.

00:0020:00000:00011:2016/06/20 14:34:12.30 kernel  Begin processing to generate RSA keypair.

00:0020:00000:00011:2016/06/20 14:34:12.33 kernel  Completed processing to generate RSA keypair.

00:0020:00000:00011:2016/06/20 14:34:12.33 kernel  Begin processing to generate RSA keypair.

00:0020:00000:00011:2016/06/20 14:34:12.38 kernel  Completed processing to generate RSA keypair.

00:0019:00000:00119:2016/06/21 00:00:12.33 server  Error: 1105, Severity: 17, State: 2

00:0019:00000:00119:2016/06/21 00:00:12.33 server  Can't allocate space for object 'DBH_SNAP_TABLES' in database 'saptools' 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 the segment.

00:0020:00000:00196:2016/06/21 00:00:12.37 server  Error: 1105, Severity: 17, State: 2

00:0020:00000:00196:2016/06/21 00:00:12.37 server  Can't allocate space for object 'DBH_SNAP_INDEXES' in database 'saptools' 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 the segment.

00:0019:00000:00151:2016/06/21 12:00:10.95 server  Error: 1105, Severity: 17, State: 2

00:0019:00000:00151:2016/06/21 12:00:10.95 server  Can't allocate space for object 'DBH_SNAP_TABLES' in database 'saptools' 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 the segment.

00:0011:00000:00162:2016/06/21 12:00:11.01 server  Error: 1105, Severity: 17, State: 2

00:0011:00000:00162:2016/06/21 12:00:11.01 server  Can't allocate space for object 'DBH_SNAP_INDEXES' in database 'saptools' 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 the segment.

tail DEV_BS.log

Jun 21 08:57:27 2016: Backup Server: 2.23.1.1: Connection from Server DEV on Host devqas with HostProcid 33752.

Jun 21 08:57:27 2016: Backup Server: 2.23.1.1: Connection from Server DEV on Host devqas with HostProcid 33752.

Jun 21 08:57:27 2016: Backup Server: 4.171.1.1: The current value of 'reserved pages threshold' is 85%.

Jun 21 08:57:27 2016: Backup Server: 4.171.1.2: The current value of 'allocated pages threshold' is 40%.

Jun 21 08:57:27 2016: Backup Server: 4.171.1.5: The current value of 'parallel scan' is 2.

Jun 21 08:57:28 2016: Backup Server: 4.41.1.1: Creating new disk file /backup/DEV/backups/21062016.dmp.

Jun 21 08:57:28 2016: Backup Server: 1.70.1.2: Configuring the shared memory per stripe to 786432 bytes.

Jun 21 08:57:28 2016: Backup Server: 6.28.1.1: Dumpfile name 'DEV1617307DF7    ' section number 1 mounted on disk file '/backup/DEV/backups/21062016.dmp'

Jun 21 08:57:28 2016: D00: Attempting direct I/O for device '/sybase/DEV/saplog_1/DEV_log_001.dat'

Jun 21 08:57:28 2016: D00: Attempting direct I/O for device '/sybase/DEV/sapdata_1/DEV_data_001.dat'

Jun 21 08:57:49 2016: Backup Server: 4.188.1.1: Database DEV: 711194 kilobytes (2%) DUMPED.

Jun 21 08:57:59 2016: Backup Server: 4.188.1.1: Database DEV: 1422384 kilobytes (4%) DUMPED.

Jun 21 08:58:18 2016: Backup Server: 4.188.1.1: Database DEV: 2133574 kilobytes (6%) DUMPED.

Jun 21 08:58:30 2016: Backup Server: 4.188.1.1: Database DEV: 2844764 kilobytes (7%) DUMPED.

Jun 21 08:58:46 2016: Backup Server: 4.188.1.1: Database DEV: 3535648 kilobytes (9%) DUMPED.

Jun 21 08:59:01 2016: Backup Server: 4.188.1.1: Database DEV: 4246774 kilobytes (11%) DUMPED.

Jun 21 08:59:17 2016: Backup Server: 4.188.1.1: Database DEV: 4957964 kilobytes (12%) DUMPED.

Jun 21 08:59:33 2016: Backup Server: 4.188.1.1: Database DEV: 5669154 kilobytes (14%) DUMPED.

Jun 21 08:59:45 2016: Backup Server: 4.188.1.1: Database DEV: 6360038 kilobytes (16%) DUMPED.

Jun 21 09:00:03 2016: Backup Server: 4.188.1.1: Database DEV: 7071228 kilobytes (18%) DUMPED.

Jun 21 09:00:19 2016: Backup Server: 4.188.1.1: Database DEV: 7782418 kilobytes (19%) DUMPED.

Jun 21 09:00:31 2016: Backup Server: 4.188.1.1: Database DEV: 8493608 kilobytes (21%) DUMPED.

Jun 21 09:00:57 2016: Backup Server: 4.188.1.1: Database DEV: 9204798 kilobytes (23%) DUMPED.

Jun 21 09:01:15 2016: Backup Server: 4.188.1.1: Database DEV: 9895682 kilobytes (24%) DUMPED.

Jun 21 09:01:31 2016: Backup Server: 4.188.1.1: Database DEV: 10611672 kilobytes (26%) DUMPED.

Jun 21 09:01:36 2016: D00: SYBMULTBUF ERROR: Wait for i/o completion failed: State=107, Msg=The i/o read call failed for device /sybase/DEV/sapdat at offset 11090788352 for size 131072 bytes. Operating system error message: Input/output error

Jun 21 09:01:36 2016: Backup Server: 4.145.2.22: [107] Error for database/archive device while working on stripe device '/backup/DEV/backups/21062016.dmp'. The i/o read call failed for device /sybase/DEV/sapdat at offset 11090788352 for size 131072 bytes. Operating system error message: Input/output error

Jun 21 09:01:36 2016: Backup Server: 4.188.1.1: Database DEV: 10869746 kilobytes (26%) DUMPED.

Please help

[Johan_sapbasis]

Hi Qazi,

You need to dump your transaction log:

dump transaction saptools to "/backup/DEV/backups/saptoolslog.dmp" with compression = 2

go

Also in sp_helpdevice afterwards check the saptools log devices for free space.

Regards,

Johan

[Former Member]

Nicolas,

u mean after dump transaction method i can run dump database as per my command??

[Former Member]

I have tried this but below error

how to disable this??

[Former Member]

Hi,

Lots of free space available.

dumping the database log?? syntax please??

[DilipVoora]

Hi Qazi,

Use the command below to turn off the option on saptools(from the log) database and then dump the transaction log.

1> use the command sp_dboption "<database_name>", "trunc log on chkpt", false

2> go

Regards,

Dilip Voora

[Johan_sapbasis]

Hi,

You need to dump transaction log, your transaction log is full sql1105. Then check the device afterwards, worst case scenario increase the size or add additional file to saptools.

Example only:

disk resize name = "saptools_data_001.dat",size = "1024M"
go

then:

alter database saptools log on "saptools_data_001",size = "1024M"

go

Just confirm the details in your db with sp_helpdb and sp_helpdevice.

Sorry for late reply, was busy with oracle prod sap down.

Regards,

[luc_vanderveurst]

Hi,

Your error messages are saying that the 'default' segment is full.

If the transaction log was full, then the message would say 'logsegment' full.

So any action on your transaction log, like dump transaction, increasing the transaction log, set the log truncation on checkpoint or not, will not help you.

You need to make free space in your database either by deleting data or by increasing the database size.

Lookup the alter database command:

alter database saptools on <device> = <size>

sp_helpdb saptools

will show what devices contain data or log.

Increase the database on a data device.

You can check if there is still  free space on the data device and if not, create a new device or alter an existing one.

Luc.

PS. the above is only true if your database has log and data on separate devices, check with sp_helpdb what your situation is.

Message was edited by: Luc Van der Veurst

[Johan_sapbasis]

Hi,

Look at these:

1749935  - SYB: Configuration Guide for SAP ASE 15.7

2199714 - Error 4208 DUMP Trasaction to a dump device is not allowed while the trunc log on chkpt option is enabled.

1999458 - DUMP TRAN command raises error Msg 4205 "Syslogs does not exist in its own segment..." - ASE

1588316 - SYB: Configure automatic database and log backups

give output results of the following:

use master

go

sp_helpdb saptools

go

I cannot recall if trunc. log on chkpt should be disable for saptools but for SID it must always be, refer to the configuration guide.

If you need to disable.

use master

go

sp_dboption SID, ''trunc log on chkpt', 'false'

go

So if you doing for saptools you would substitute your values as required,

Kind Regards,

[Johan_sapbasis]

Hi,

Look into setting log thresholds, to trigger automatic dumps this is what I did on the client side.

Kind Regards,

Johan

[Former Member]

Check attached screenshots

so do u think it will resolve db resize and alter db as u suggested earlier??

[Johan_sapbasis]

Hi,

use master

go

disk resize name = "saptools_log_001.dat",size = "800M"

go

alter database saptools log on "saptools_log_001",size ="800M"

go

Regards,

Johan

[Former Member]

Johan, its giving below error,

check my syntax?