Skip to Content
avatar image
Former Member

recognize full dump

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Feb 23, 2018 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 ..?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 23, 2018 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

    Add comment
    10|10000 characters needed 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"



  • Feb 23, 2018 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


    Add comment
    10|10000 characters needed characters exceeded

  • Mar 09, 2018 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.



    Add comment
    10|10000 characters needed characters exceeded