on 06-19-2016 10:02 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI,
try without the .dat
johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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,
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
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,
User | Count |
---|---|
78 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.