This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

HowTo: Call sp_dump_history with Purge in ASE 16.0 SP03

Due to a bug in ASE 16.0 SP03 PL08 (and maybe earlier versions) the sp_dump_history call does not work properly.
It just returns an empty screen.
When you look at the procedure code, you will see that it fails to find the tempdb for whatever reason.

We can use the sp_dump_history procedure to look at the dump history (backup history) for an ASE database.
From ASE 15.7 onwards we can also use it to purge the history, reducing the size of the dumphist file and in some cases improving the performance of backups themselves (see SAP note 2753062 for details on issues).

By prefixing the tempdb onto the call from the master database, you can get sp_dump_history to work as it should.

Below is an example that purges all backup records from the dump history file (dumphist) from before January 29th @ 15:00:00 :

use master
go

tempdb..sp_dump_history @operation='purge', @until_time="january 29,2020 15:00:00:00"
go

Tip: In Linux the correct date format can be obtained with the date command:

date "+%b %d,%Y %T"

As another tip, you can use the sp_helptext procedure to look at the source code of any stored procedures. Take a look on help.sap.com for the parameters for sp_helptext.

SAP ASE Backup Server Error Writing to Archive

When running SAP Business Suite on the SAP ASE database platform, I was trying to dump and load from one database to another database.
A backup server error was seen on the target database and in the backup server log file (<SID>_BS.log) during the LOAD statement execution:

Backup Server: 4.145.2.22: [3] Error for database/archive device while working on stripe device ‘/<file1>’. Error writing to archive device /<file1>. Attempted to write 65536 bytes, 32768 bytes were written.

This specific issue turned out to be caused by my target DB not having exactly the same size for the data or log devices.

I even found in some cases that the log device needed to be a tiny little bit bigger (we’re talking about 1MB bigger) than the source database.