cancel
Showing results for 
Search instead for 
Did you mean: 

recognize full dump

Former Member
0 Kudos

I've a question about making a full dump.
We run transactional dumps every 60 minutes, this is written to a log.

Then, as a second step, we search in the log for the pattern "Use DUMP DATABASE instead". If this exists, it's a hint that a full dump is necessary.

my question is: is this the prefered way to detect a full dump?

thank you

Accepted Solutions (0)

Answers (4)

Answers (4)

0 Kudos

Let me confirm I understand your situation correct:

You do a DUMP TRAN on an ASE database on an hourly basis.
Full database dumps are performed whenever the DUMP TRAN fails due to the above error message.

Is this correct ?

Your question is whether that is the pereferred way to detect that a full dump is necessary.
Actually I would perform a FULL DB DUMP on scheduled regular basis that addresses your needs wrt to time to online in case you need to restore the system.

Additionally you must get a full db dump every time something cause the transaction log sequence to be broken - to be on the save side , this must be taken wheil the application is offline ( other wise you run the risk of loosing transactional data that was generated before the full dump finished successfully) . So for production databases it is recommened to make sure TX log sequence does not get broken during online time.

In order to prevent any minimally logged statements review database option:

enforce dump tran sequence # ensure nojn one can accitentally run something that breaks TX log sequence

When you set that , you might need to schedule activity that does break log sequnce to times when apploication is offlline and a full DB can be taken directly afterwards
Also , review in how far these DB options might help:

full logging for all
full logging for alter table
full logging for reorg rebuild
full logging for select into

Note 'any of these full logging for ... ' options will increase the amount of logs written to the TX log.

HTH

Tilman

Former Member
0 Kudos

Hello Bret, thanks for your answer, but how can I use an if statement in a isql statement?
Or is your idea to log the result out, and write the if seperately in a bash script for each database?

And I tried ths out, eg. I get

-----------
32

(1 row affected)

According to your link, it states

32 – Truncate-only dump tran has interrupted any coherent sequence of dumps to dump devices.

How could I interpret this? Is now a full dump required or not ..?

former_member188958
Active Contributor
0 Kudos

Hi Niki,

Here is the general documentation on the IF THEN ELSE statement in TSQL:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1600/doc/html/san1390612383583...

My example was just a general outline / pseudocode. Here is a working implementation:

if (select tran_dumpable_status("test") ) = 0
print "tran log is dumpable"
else
print "tran log is not dumpable"
go

Cheers,
-bret

M-B
Employee
Employee
0 Kudos

Hi,

There is a couple of scenarios when you get "Use DUMP DATABASE instead" message :

  1. the transaction log is completely full and not on its own device
  2. after executing minimally logged transactions like "select into", fast bcp, parallel create index.

If you are taking tran dumps every hour, we can exclude scenario 1. This means that you are doing minimally logged transactions as in 2 and which should raise an error message in the Backup Server log.

If you are scripting your tran log dumps, then best is to test for the error and execute a dump database automatically.

Hope this helps

Mehrab

former_member188958
Active Contributor
0 Kudos

There is a built-in function named tran_dumpable_status("database_name") for checking if the transaction log is currently dumpable or not,

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1600/doc/html/san1393050504617....

Instead of trying to run dump tran and then checking for failure, your dump process can use logic like

if tran_dumpable_status(mydatabase) = 0

then

dump tran mydatabase to tranlog_archive_device_name

else
begin
dump tran mydatabase with truncate_only
dump database mydatabase to db_archive_device_name
end

former_member89972
Active Contributor
0 Kudos

We use the tran_dumpable_status exactly the way Bret has pointed out.

Just make sure that you exclude "tempdb" and likes from doing a full database dump inadvertantly.

In the "else" clause above the second statement has that "potential" !

In general handle "temporary databases with care to do only "dump tran with truncate_only"

HTH

Avinash