Skip to Content
0

How do we get Commvault to do our Sybase ASE backups correctly (truncate_only on logs)

May 11, 2017 at 09:15 AM

224

avatar image

Hi

We're having some difficulties setting up Commvault for our Sybase ASE SAP systems. The difficulties are to do with the fact there's multiple databases to backup, and the transaction log backups that go with them - or in some cases, the 'truncate_only' option that's required.

I.e. here's some we need to backup:

dump database SDP using config = 'SDPDB'
dump database master using config = 'SDPDB'
dump database saptools using config = 'SDPDB'

etc..

trans logs:

dump transaction SDP using config = 'SDPLOG'
dump transaction master using config = 'SDPLOG' with truncate_only

Note that there's no trans logs for saptools, and that there's no backup of the trans logs for master as such - it's a "truncate_only". This is supposed to be ran immediately after the master backup. However, when you try and set up backups in Commvault, you have to tick/untick the transaction log 'do not truncate log' option (as per http://documentation.commvault.com/commvault/v10/article?p=products/sybase/backup_adv.htm *) for the whole backup set.

So really we'd need to create a couple of backup sets, one for SDP and any others that need a trans log backup, and another for those that need truncating, such as master - right?

However, Commvault won't let us do that. It wants to add in all of the databases, not just one or a selection. The interface isn't 'friendly' or intuitive; we found we had to keep going back between screens, and at one point, we'd managed to add in a selection of the databases we wanted to do a truncate on (i.e., master, model, sybmgmtdb, sybsystemdb, sybsystemprocs) but when we saved it, came out and came back in, it had added the rest (SDP, saptools etc). That's pretty dangerous - we don't want to do a truncate on our SDP (main SAP DB) trans logs!

Also having two different set isn't ideal, the truncate is supposed to be done at the same time/immediately after. Is there any way to group this?

The only option we thought of was calling our backup script (which contains the correct sequences of backups, trans logs backups and truncates) directly from CommVault; not sure we can do this (?) and even if we could, it would just backup to disk/whatevers in our script config and not to CommVault.

Any thoughts/suggestions? I think one of the main problems here is that there's very few people using CommVault with SAP on Sysbase ASE...

Thanks
Ross

P.S. *Going back to the document, does the following sentence make any sense to anyone?
It doesn't seem to be correct nor make any sense to me:

"Back Up Transaction Log of Damaged Database (Do Not Truncate Logs)
In Sybase server, some databases may not have log devices. By default, when you perform a log backup, the system performs log backups of those databases that possess transaction logs. Hence, it is necessary to perform a full backup after a log backup. A full backup will perform a backup of all databases irrespective of the fact that they may or may not possess transaction logs."

I'm not even sure this is how you do the "truncate_only" option, from that wording! Do Commvault actually understand how ASE works?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bret Halford
May 11, 2017 at 01:01 PM
0

Hi Ross,

I don't have any direct experience with Commvault. However, my impression from the documentation you point to is that this "do not truncate log" checkbox option would be to invoke the "dump tran with no_truncate" option. This option is very rarely used, it is for cases where you have the data and log segments on different devices and experience a failure of a data device; the option allows you to dump the log records from the log devices even if the system tables aren't available due to the bad data device. This also ties in with your "p.s." question, the section header there is definitely talking about the case where there is a damaged device. I don't think you should be checking this box.

A normal dump tran command both copies the log records to an archive device and truncates the inactive part of the log (from the beginning of the log to the start of the oldest open transaction).

It sounds like Commvault is expecting that data and log segments/devices will be separated (as they must be) for database that "dump tran to archive" will be done for, and if data and log is mixed it either does nothing (expecting you to use the trunc log on ckpt database option) or does a dump tran with truncate_only. It sounds like the full backup option does a full dump database on all databases (which matches your experience of databases being automatically added to the dump set).

Show 10 Share
10 |10000 characters needed characters left characters exceeded

Hi Bret!

Thanks for the response. That's a very good point; I'd forgot about the 'no_truncate' option and just assumed the checkbox was for the 'truncate_only' option. That paragraph makes sense then now!

Right, so the question then becomes, "how do we specify truncate_only from CommVault?". I think you are implying then that perhaps CommVault just 'does it', so if you choose a trans backup for the all the databases (master, model etc etc) perhaps it does the trans backups for the ones it needs to and the truncate_only for the others, i.e. does the following:

dump transaction master using config = 'SDPLOG' with truncate_only
dump transaction model using config = 'SDPLOG' with truncate_only
dump transaction sybmgmtdb using config = 'SDPLOG' with truncate_only
dump transaction sybsystemdb using config = 'SDPLOG' with truncate_only
dump transaction sybsystemprocs using config = 'SDPLOG' with truncate_only
dump transaction SDP using config = 'SDPLOG'

I guess that would make sense. We will have to try it I suppose, then wipe the system and try a restore! (will be taking some manual non-CommVault backups to disk first though!).

Thanks again
Ross

0

Alternatively, you could install an ASE with a development license and test commvault against that with different combinations of databases (mixed data and log, separate, truncate long on ckpt on/off) on that instead of the main system. :-)

1

We finally had a chance and ran a transaction log backup on all the databases of our ASE system from CommVault. The logs there showed a successful trans log backup for our main SAP system (i.e. the ASE database with the matching <SID>).

For the other databases, it said it had skipped them - for saptools and sybsecurity because "trunc log on chkpt was set to true" and for the others because "transaction log existed on the same device as the data" - so, all good, as expected.

Still unsure though whether it then did a truncate logs on all those required (i.e. all except <SID>, saptools and sybsecurity). Didn't say anywhere in the CommVault log.

Is there any Sybase log we can check? In the BS_<SID>.log it only shows full database backups and not transaction log backups (is there a separate log file anywhere for transaction log backups?) so we couldn't see there; in DBACOCKPIT in SAP we could see the transaction log backup for <SID> but it didn't show any others (or any truncate commands etc).

Just not convinced that CommVault is carrying out the necessary truncate. If it's not, over time the master transaction logs could build up and the system could stop due to space issues. Is there any way to check?

0

Just before and after the transaction log back, do an sp_spaceused syslogs or select count(*) syslogs.

The values would decrease when the log was truncated.

-bret

1

Hi again Bret

Thanks for the answer, but no joy I'm afraid. I tried on two systems and always get the same results, i.e.:

1> sp_spaceused syslogs
2> go
name total_pages free_pages used_pages reserved_pages
--------------- --------------- --------------- --------------- ---------------
syslogs 19200 17158 76 0

and

1> select count(*) syslogs
2> go
syslogs
-----------
1

before and after doing a master trans log truncate (doesn't change)

i.e.
dump transaction master using config = 'TDDLOG' with truncate_only

Is this perhaps just down to the fact that this is a quiet system, I've previously done a truncate on the master trans logs, and it's not created any since? The 'low' value of 1 for syslogs potentially implies that...?

0
Hi Ross,

Actually, the query here is wrong, it should be "select count(*) FROM syslogs". What you has is just "select count(*)" by itself with the resulting column given the alias name 'syslogs' - I..e is it equivalent to just running just "select count(*)". [hmm. wonder why that returns 1 rather than 0 for an empty set?]


But not having any activity since you last truncated the log would certainly be an issue with this check.
You should generate some log records that can be truncated. What I usually do is create a table with a single char() field with a width that is at least half of the servers page size (@@maxpagesize). i.e. on a 4K server, I would use
create table t1 (c1 char(2048) not null)
and then insert 8 rows
insert t1 values ("a")
go 8
drop table t1
go

As each inserted row is more than half the width of a page, each insert uses up a full log page, so this sequence guarantees that the log will have allocated a new extent, leaving the previous extent truncatable (assuming no other open transactions in the database).

Cheers,
-bret

1

Thanks Bret, that works - should now be able to check that CommVault is doing as it should! :)

0

Was finally able to test this... ran the inserts to increase the sys log entries, then ran a transaction log backup on all databases from CommVault.

Syslogs didn't decrease.

Ran a manual "dump transaction master using config = 'TDDLOG' with truncate_only" and saw syslogs decrease.

So CommVault ISN'T doing a truncate unfortunately when doing a trans log backup of everything.

I also did the same when a full backup (on all databases, including master) was run from CommVault, just in case it did something 'clever' and knew that after a full backup it needed to truncate the master trans logs... And the value of syslogs dropped! So perhaps it did a truncate of the master trans logs then as it 'knew it should' after doing a full backup of master?!?

0

Hi Ross,

You should be able to use the monSysSQLText monitoring table to observe the actual commands that CommVault is sending to ASE.

-bret

0

Thanks Bret, that's awesome! Enabled monitoring and managed to capture this during the full online master backup (no trans logs) from CommVault:

use master
select segmap, lstart, vstart, size,vdevno from sysdatabases db, sysusages u where db.dbid = u.dbid and db.name = "TDD"
select low, high,vdevno, status, cntrltype, name, phyname from sysdevices where vdevno = 11 and cntrltype = 0
select low, high,vdevno, status, cntrltype, name, phyname from sysdevices where vdevno = 5 and cntrltype = 0
select low, high,vdevno, status, cntrltype, name, phyname from sysdevices where vdevno = 5 and cntrltype = 0
dump database master to "SybGalaxy:: -file /simpana/simpana/Base/Temp//sybGalaxyParams844792_0"
stripe on"SybGalaxy:: -file /simpana/simpana/Base/Temp//sybGalaxyParams844792_1"
stripe on"SybGalaxy:: -file /simpana/simpana/Base/Temp//sybGalaxyParams844 792_2"
stripe on"SybGalaxy:: -file /simpana/simpana/Base/Temp//sybGalaxyParams844792_3" with blocksize=1048576
dump transaction master with truncate_only

I.e. it DOES do a truncate_only as part of the full backup!

Good to know :)

Many thanks for your help with all this, feel I'm starting to understand ASE a (little) bit more now!

0
Yogesh Patel
May 11, 2017 at 03:50 PM
0

Hello Ross,

As far I know

Option : 1

You need CommVault agent for Sybase database (If CommVault have it!!) If that agent installed on DB server and you taking backup it will truncate logs automatically after successful log backups

Option : 2

If this option is not available please backup DB and log through Sybase scripts at file system and take backup of those DB and log dumps through CommVault.

Note: if you go option 2 make sure you cleanup filesystem regularly.

Regards,

Yogesh

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Yogesh, I believe we have the agents installed. We will try some backups today and see exactly what it does.

0
Ross Armstrong Jun 08, 2017 at 09:13 AM
0

As per exchange with Bret, the answer is:

You set up CommVault to backup ALL the databases, and create two schedules: one to do a full online backup of them, and one to do a transaction logs of them.

CommVault 'knows' which DBs have trans logs.

The full online backup handles the necessary 'truncate_only' of master (not done as part of the trans log backups) (as well as model & saptempdb).

Share
10 |10000 characters needed characters left characters exceeded