Schedule Backups

[Former Member]

Hello,

I´m following the note 1588316 - SYB: Configure automatic database and log backups

I´ve created the stored procedures for backup and log. I´ve launch (1> dump database P01 using config = 'P01DB' 2> go) for Backup database and the dump file was created correctly but If I try to do the same with log stored procedure(1> dump transaction P01 using config = 'P01LOG' 2> go)

I get this message error:

DUMP TRANsaction to a dump device is not allowed while the trunc. log on chkpt. option is enabled.  Disable the option with sp_dboption, then use DUMP DATABASE.

It´s mandatory to execute the different parameters for make the backup?

The process will be this:

use master

go

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

sp_dboption SID, 'full logging for all', 'true'

     After that I must to do a Backup of the database with the dump script, isn´t? Then I need to execute the follow parameter

sp_dboption SID'enforce dump tran sequence', 'true'

And then I will be able to make the dump of the DB and Transaction log? Each time that backup of log is made the space of syslog file is empty (truncated/empty)? There was defined by default value of 12 GB

For execute the Script on dbacockpit like a job in the screen of SQL script I must to put this, isn´t?

dump database P01 using config = 'P01DB'

go

Are there any option for backup the transaction log when is about 90% busy?

I must to create another stored procedured for the backup of db master?

Thanks

[Former Member]

I´ve enabled the transaction log and scheduled the job on dbacockpit. Every time that the dump of log is made to the filesystem, the transaction log is empty? OR I need to do an extra operation?

thanks

[former_member188958]

When you dump the transaction log, ASE truncates the inactive part of the log.  That is, from the start of the syslogs page chain up to the page containing the BEGIN transaction log record for the oldest open transaction.

Depending on circumstances, that might mean that nothing was truncated (the oldest open transaction now is the same as the oldest open transaction the last time the log was truncated), or it might mean that all but the last page of the log was truncated.

Under normal conditions, you don't need to do any extra operations.   If the log has filled (1105 errors on the log segment), you might have to manually force the oldest open transaction to roll back by issuing a KILL against it, or provide more disk space for the log by issuing an ALTER DATABASE LOG ON command.

-bret

[Former Member]

Thanks Bret,

The datafile for transaction log have the default size that is 12 GB. I´ve scheduled the in dbacockpit the script to dump the transaction log every 8 hours. Then If datafile have 3GB used and the script to dump the database this space is is recovered, isn´t?

When I schedule a script on dbacockpit, when it´s executed I get the first script that has been failed:

--SAP DBA Planning Calendar: SQL Script

print( SELECT "starting job execution at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

dump transaction SID using config = 'SIDBackup'

go

print( SELECT "job execution finished at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

And the second script has finished successfully:

Backup Server: 3.42.1.1: DUMP is complete (database SID).

----------------------------------------------

job execution finished at 2013-11-21 00:01:48

(1 row affected)

Are there any problem on dbacockpit managing sybase scripts?

[former_member188883]

Hi Ruben,

Can you refer this thread http://scn.sap.com/message/14384700

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Thanks Deepak,

I´ve scheduled a job with the sql script:

1>dump database P01 using config = 'P01DB'

2> go

Then in DBA Planning calendar I've to entries one job finished incorrectly and other correctly.

The first:

--SAP DBA Planning Calendar: SQL Script

print( SELECT "starting job execution at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

--SAP DBA Planning Calendar: SQL Script

print( SELECT "starting job execution at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

dump database KDE using config ='P01DB'

go

print( SELECT "job execution finished at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

print( SELECT "job execution finished at " || str_replace(convert(VARCHAR(20),getdate(),23),"T","") )

Second:

Changed database context to 'saptools'.

----------------------------------------------

starting job execution at 2013-11-18 22:00:22

(1 row affected)

----------------------------------------------

starting job execution at 2013-11-18 22:00:22

(1 row affected)

Backup Server: 4.171.1.1: The current value of 'reserved pages threshold' is 85%.

Backup Server: 4.171.1.2: The current value of 'allocated pages threshold' is 40%.

Backup Server: 4.171.1.5: The current value of 'parallel scan' is 2.

Backup Server session id is: 10. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

Backup Server: 4.41.1.1: Creating new disk file D:\sybase\sid\copies\SID.DB.20131118.220030.000.

Backup Server: 6.28.1.1: Dumpfil

e name 'SID133221357E ' section number 1 mounted on disk file D:\sybase\sid\copies\SID.DB.20131118.220030.000'Backup Server: 4.188.1.1: Database SID: 823470 kilobytes (2%) DUMPED.Backup Server: 4.188.1.1: Database SID: 1647048 kilobytes (3%) DUMPED.Back

Backup Server: 4.188.1.1: Database SID: 9016346 kilobytes (12%) DUMPED.Backup Server: 4.188.1.1: Database KDE: 9839924 kilobytes (13%) DUMPED.Backup Server: 4.188.1.1: Database SID: 10663390 kilobytes (14%) DUMPED.Backup Server: 4.188.1.1: Database SID: 1

: 18856266 kilobytes (24%) DUMPED.Backup Server: 4.188.1.1: Database SID: 19669090 kilobytes (25%) DUMPED.Backup Server: 4.188.1.1: Database SID: 20492668 kilobytes (27%) DUMPED.Backup Server: 4.188.1.1: Database SID: 21316246 kilobytes (28%) DUMPED.Backu

Backup Server: 4.188.1.1: Database SID: 29509010 kilobytes (38%) DUMPED.Backup Server: 4.188.1.1: Database SID: 30321834 kilobytes (39%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 31145412 kilobytes (40%) DUMPED.Backup Server: 4.188.1.1: Database  SID:

DE: 39338288 kilobytes (50%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 40161754 kilobytes (52%) DUMPED.Backup Server: 4.188.1.1: Database KDE: 40974690 kilobytes (53%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 41798268 kilobytes (54%) DUMPED.Bac

Backup Server: 4.188.1.1: Database  SID: 49167614 kilobytes (63%) DUMPED.Backup Server: 4.188.1.1: Database KDE: 49991192 kilobytes (64%) DUMPED.Backup Server: 4.188.1.1: Database KDE: 50814770 kilobytes (65%) DUMPED.Backup Server: 4.188.1.1: Database  SID:

DE: 59011000 kilobytes (74%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 59828304 kilobytes (75%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 60645608 kilobytes (76%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 61462912 kilobytes (77%) DUMPED.Bac

Backup Server: 4.188.1.1: Database  SID: 68840156 kilobytes (85%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 69657460 kilobytes (85%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 70474764 kilobytes (86%) DUMPED.Backup Server: 4.188.1.1: Database  SID:

DE: 78669312 kilobytes (95%) DUMPED.Backup Server: 4.188.1.1: Database SID: 79497370 kilobytes (96%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 80314674 kilobytes (97%) DUMPED.Backup Server: 4.188.1.1: Database  SID: 81131978 kilobytes (98%) DUMPED.Bac

Backup Server: 3.43.1.1: Dump phase number 2 completed.

Backup Server: 3.43.1.1: Dump phase number 3 completed.

Backup Server: 4.188.1.1: Database SID: 83943598 kilobytes (100%) DUMPED.

Backup Server: 3.42.1.1: DUMP is complete (database  SID).

----------------------------------------------

job execution finished at 2013-11-18 22:47:34

(1 row affected)

----------------------------------------------

job execution finished at 2013-11-18 22:47:34

(1 row affected)