Skip to Content

The challenge

Since10th may, I started on a project that in a few words is very simple (Baseline on ECC EH6) but with some challenges to win. One of then was the backup strategy without increase the actual amount payed yet by the customer.

The scenery is a Baseline build on a Sybase ASE 15 server. In general lines, this database is not to hard to administrate, but nothing is very easy as look. I looked for too many documentations and blogs to help me on this task, and what helped me was a blog of Sybase. I dont have the address of it, but when I find it again, i will post its address.PS_002 May. 31 16.34.jpg

On this blog, one information that help me too much was that Sybase has GUI to administrate it and it is installed when you install the client. The next step was found it on the server and put it running. For who is looking for it, it is installed on %Sybase install dir%\<SID>\Shared\Sybase Central 6.0.0\win64\scjview.exe.

Once connected to the database thought this wonderful tool, I could look for some solutions on it.

Mr Halford, Bret (http://scn.sap.com/people/bret.halford) answered a post that was very useful due in this post he comments about backup strategies and Sybase commands for that (http://scn.sap.com/thread/3358549). With this blog and too many research i could find a good option for backup the SAP database.


The first step was determine the correct command to backup the database:

You can do a full database backup using  the command “dump <database> to <path to backup including the filename>”.

This option, on a quad xeon 2,4 and 100G database, took around 8 hours to finish it. Takes too long if you compare it with Oracle that takes 30 min or less.


The challenge changed to decrease the time of the backups… I could find a store procedure provided by Sybase and available from the installation that using it I could decrease the total time to 40min.

Its name is sp_jst_dump_dbs_to_disk and declaring some parameters, I could split the dump to 8 parts, use compression reducing the total disk used by 60% and time to finish the Dump to 40 min.

For this project, I created a store procedure to keep the command as more simple to day-by-day tasks, converting a few lines of command into a single line.

Here I past the entire procedure.

Procedures

PS_001 May. 31 16.29.jpg

create procedure dbo.Backup_Database
/* [ (@param_name datatype [=default] [output] ), … ] */
as
begin
declare @db_names varchar(500),
@dump_location varchar(128),
@use_srvr_name int,
@date_fmt int,
@stripe_cnt int,
@compress_value Int,
@js_runid Int

select @db_names=’Your DATABASE’,
@dump_location=’LOCATION TO THE DUMP’,
@use_srvr_name=0,
@date_fmt=3,
@stripe_cnt=8,
@compress_value=6

declare @user_code int
exec @user_code=sybsystemprocs..sp_jst_dump_dbs_to_disk @db_names,
@dump_location, @use_srvr_name, @date_fmt, @stripe_cnt, @compress_value
if( @user_code < 0 )
begin
print “ase_js_cmd: sp_sjobsetstatus @name=’%1!’,
@option=’exit_code=2, user_code=%2!'”,
@js_runid, @user_code
end
else
begin
print “ase_js_cmd: sp_sjobsetstatus @name=’%1!’,
@option=’exit_code=1, user_code=0′”,
@js_runid
end
end

I created to another Procedure, but for backup of the Transaction Logs, using the same way

create procedure dbo.Backup_Transaction_log
/* [ (@param_name datatype [=default] [output] ), … ] */
as
begin
declare @db_names varchar(500),
@dump_location varchar(128),
@use_srvr_name int,
@date_fmt int,
@compress_value int,
@truncate_flag int,
@row_threshold int,
@time_threshold varchar(5),
@js_runid int

select @db_names=’YOUR DATABASE’,
@dump_location=’PATH TO BACKUP’,
@use_srvr_name=0,
@date_fmt=4,
@compress_value=6,
@truncate_flag=0,
@row_threshold=0,
@time_threshold=’0mi’

declare @len int
, @end_pos int
, @db_name varchar(30)
, @user_code int
select @len=isnull(datalength(@db_names), 0)

–loop through each db specified
while @len > 0
begin
–get the filename, only, next or last
select @end_pos=patindex(‘%[, ]%’, @db_names )
if @end_pos <=0
begin
— only or last db_names
select @db_name=rtrim(ltrim(@db_names))
select @len=0
end
else
begin
— get the next name
select @db_name=substring(@db_names, 1, @end_pos-1)
select @db_name=ltrim(rtrim(@db_name))
select @db_names=stuff(@db_names, 1, @end_pos, NULL)
select @len=isnull(datalength(ltrim(@db_names)), 0)
end
print @db_name

exec @user_code=sybsystemprocs..sp_jst_dump_tran_logs_to_disk @db_name,
@dump_location, @use_srvr_name, @date_fmt, @compress_value,
@truncate_flag, @row_threshold, @time_threshold
if( @user_code < 0 )
begin
print “ase_js_cmd: sp_sjobsetstatus @name=’%1!’,
@option=’exit_code=2, user_code=%2!'”,
@js_runid, @user_code
end
else
begin
print “ase_js_cmd: sp_sjobsetstatus @name=’%1!’,
@option=’exit_code=1, user_code=0′”,
@js_runid
end

end

end

———————————————————————————————————————

The last step was schedule this two procedures on DB13 using the backup schema defined by project.

I guess that this post became useful to others in route to Sybase.

Best Regards!

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Ruthvik B S

    Hi All,

    I am getting below error in my Backup server error log.

    SYBMULTBUF ERROR: Attempt to start database device i/o failed: State = 115 Msg = Not enough space

    Does someone know what I should do next ????

    (0) 
    1. Bret Halford

      This probably means you are dumping to a file system and that file system

      is nearly or totally full.

      If you are on a unix system, you can check on how full the file system is

      with  “df -k”

      You might need to archive some file system contents to tape or delete unneeded files to free space.  You could also try dumping to some other directory.

      (0) 

Leave a Reply