Skip to Content

recognize full dump

Feb 23 at 10:36 AM


avatar image
Former Member

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

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

4 Answers

avatar image
Former Member Feb 23 at 05:55 PM

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


(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 ..?

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

Hi Niki,

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

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"
print "tran log is not dumpable"


Bret Halford
Feb 23 at 11:14 AM

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

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


dump tran mydatabase to tranlog_archive_device_name

dump tran mydatabase with truncate_only
dump database mydatabase to db_archive_device_name

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

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"



Mehrab Bucktowar
Feb 23 at 11:39 AM


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


10 |10000 characters needed characters left characters exceeded
Tilman Model-Bosch
Mar 09 at 03:55 PM

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.



10 |10000 characters needed characters left characters exceeded