Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1

    Unanswered: dump tran getting failed frequently

    Hi DBA's

    I am new to this env and more frequently i am getting mails and error messages that DUMP TRAN got failed for a database.

    The log is something like below , could any one assist me on this what actions to be taken to avoid this issue

    00:00000:00045:2014/02/12 06:51:31.96 server Error: 4221, Severity: 16, State: 1
    00:00000:00045:2014/02/12 06:51:31.96 server DUMP TRANsaction to a dump device is not allowed where a truncate-only transaction dump has been performed after the last DUMP DATABASE. Use DUMP DATABASE instead.
    00:00000:00045:2014/02/12 06:51:31.96 server background task message: DBPan.tran.140212065131--THRESHOLD: Failed to dump to '/sybdump/GORDON_UTV_SDS/DBPan.tran.140212065131--THRESHOLD'
    00:00000:00045:2014/02/12 06:51:32.04 server background task message: DBPan.tran.140212065131--THRESHOLD: Log Dump Error : Truncated log with truncate only
    00:00000:00045:2014/02/12 06:51:32.04 server background task message: DBPan.tran.140212065131--THRESHOLD: Log dumped to device 'waste basket'
    00:00000:00045:2014/02/12 06:51:32.04 server background task message: DBPan.tran.140212065131--THRESHOLD: Log dump sizes: Before: '47350', After '24'
    00:00000:00045:2014/02/12 06:51:32.04 server background task message: DBPan.tran.140212065131--THRESHOLD: Log dump times: 06:51:31 , 06:51:32
    00:00000:00034:2014/02/12 07:18:41.42 server Space available in the log segment has fallen critically low in database 'DBPan'. All future modifications to this database will be suspended until the log is successfully dumped and space becomes available.
    00:00000:00045:2014/02/12 07:18:41.42 server background task message: DBPan.tran.140212071841--THRESHOLD: Logspace left in database '3656'
    00:00000:00045:2014/02/12 07:18:41.42 server Error: 4221, Severity: 16, State: 1
    00:00000:00045:2014/02/12 07:18:41.42 server DUMP TRANsaction to a dump device is not allowed where a truncate-only transaction dump has been performed after the last DUMP DATABASE. Use DUMP DATABASE instead.
    00:00000:00045:2014/02/12 07:18:41.42 server background task message: DBPan.tran.140212071841--THRESHOLD: Failed to dump to '/sybdump/GORDON_UTV_SDS/DBPan.tran.140212071841--THRESHOLD'
    00:00000:00045:2014/02/12 07:18:41.54 server background task message: DBPan.tran.140212071841--THRESHOLD: Log Dump Error : Truncated log with truncate only
    00:00000:00045:2014/02/12 07:18:41.54 server background task message: DBPan.tran.140212071841--THRESHOLD: Log dumped to device 'waste basket'
    00:00000:00045:2014/02/12 07:18:41.54 server background task message: DBPan.tran.140212071841--THRESHOLD: Log dump sizes: Before: '47351', After '24'
    00:00000:00045:2014/02/12 07:18:41.54 server background task message: DBPan.tran.140212071841--THRESHOLD: Log dump times: 07:18:41 , 07:18:41



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

  2. #2
    Join Date
    Feb 2012
    Posts
    133
    Hi there.
    when was the last full database backup? I think you should dump the database first before dumping the transaction log.

  3. #3
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    makecompile is correct.
    In order to be able to do sequencial Tran dumps that can be loaded if needed. You need to have a "starting point" thats the full backup. After that there is a timeline for your tran dumps to follow.
    Also if you are truncating the log, by db setting or manually, you will loose the chain of the tran dumps.
    So if you are implementing incremental backup, make sure your threshold procedure is the only one dumping the tran log, in order no to "break" the chain.
    Last edited by Catarrunas; 02-13-14 at 03:20.

  4. #4
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1

    updated status for tran dumps

    The current status is what i say today , the last dump till yesterday afternoon it happened with out any issues.

    can i just take dumps now for all the databases

    and request to guide me in taking the dumps for TRANS manually .
    please explain me the process from taking dumps of full datbases to tran dumps

    DB DUMP STATUS :
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.sybsystemprocs: Database Dump FAILED (NOT DONE) 14-02-12.10:42:17
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.dbccdb: Database Dump FAILED (NOT DONE) 14-02-12.10:41:35
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.model: Database Dump FAILED (NOT DONE) 14-02-12.10:41:56
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.master: Database Dump FAILED (NOT DONE) 14-02-12.10:41:46
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.sybsystemdb: Database Dump FAILED (NOT DONE) 14-02-12.10:42:06
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Database Dump FAILED (NOT DONE) 14-02-12.10:41:24

    TRANSACTION DUMP STATUS :
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.10:41:24
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.dbccdb: Tran Dump FAILED 14-02-12.07:29:33
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.11:33:01
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.10:41:24
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.10:41:24
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.10:41:24
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED 14-02-12.07:29:22
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem_Archive: Tran Dump FAILED (NOT DONE) 14-02-12.10:41:24
    dumpmessage: DUMP_ERROR GORDON_UTV_SDS.ARSystem: Threshold dumping Trans FAILED UNKNOWN

  5. #5
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Take a full dump of dbs,
    ARSystem_Archive
    ARSystem

    How? I dont know the backup process you have ( if is manual, i doubt, if is an utility of some sort)
    You can do an Dump, it wont put the db offline is available for users.


    Also show us the db_options on those 2 dbs.

    Also you log there i dont really get it:
    For ARSystem_Archive there is a dump tran and a dump database at the same time "14-02-12.10:41:24" ??

    Why was the database dump failing? (space on fs perhaps)

    Give us more errors details.


    The main flow will be for example (ARSystem - this are hipothetical timings):
    Database dump ARSystem at 06:00
    Dump transactions during the day depending on the transactions fill the log or not, lets say:
    11:00
    14:40
    16:12
    19:55



    If the transactions during the day are not all dumped to disk, if something gets truncated, you wont be able to continue with the dump trans.

    Lets say if got truncated manually at 15:00 hours, after that you wont be able to save more transactions and in case of recovery you will only recover until the 14:40, that is the latest with the uninterruped sequence.

  6. #6
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1
    I have attached the information which was mentioned by you ,
    alternatively from one of the cron job mail i have found that dumps are running fine, but when i see in the /sybdump/instancename, there are no dumps for the day to happen

    any how i am in confusion , many many thanks for the support you are providing .

    Please assist me in completely in closing this issue.
    Last edited by rmajeti; 05-26-14 at 13:24.

  7. #7
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    So you have a conflict here. It can be solved.

    Your errorlog is telling you the problem, which is the same we told you here.
    Somehow ( will get to how later ) you log is being truncated, this means that the information on the rows logged is being dumped/flushed, it disappears is lost.

    So when your threshold is met, and you procedure tries to save the transactions logs on disk it can't because it does not have all the information, because it was previously lost ( truncated ).

    The issue here is that your DB, has the option "trunc log on chkpt" active.
    So everytime there is a checkpoint your log is truncated/flushed, keeping impossible to save trans to disk. Like i said before "you loose the chain", how can he keep the log of now if i don't know what happened until now? It can't reconstruct anything if necessary.


    First thing to do, dump databases. ( full dump ). Try to run the script you have on Cron manually if possible. the middle one.


    Now you have 2 options, depending on your backup policy.
    If you need transaction logs dumped to disk, to enable recovery into a point in time, disable the "trunc log on chkpt" on your db.

    If you really don't need such specific recovery, if one/two dump per day is enough for the customer, you need to change you threshold procedure to verify if your db has or not the "trunc log on chkpt" option set, and act accordingly. ( meaning if it has it will truncate log and not try to save it to disk )


    For the archive DB, just do the full dump, the errors should not occur if a truncate tran is not manually performed.

    PS: from what we can see on the Cron, your case is the 1st. You run the dump trans every 30 mins +-. ( what does that do? saves to disk i suppose )
    Is a DR measure.
    Last edited by Catarrunas; 02-13-14 at 08:55.

  8. #8
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1

    same issue again

    Sorry for the late response to you , thanks for the help you are providing

    I have followed your advice and implemented the same

    1. i have taken the backups manully and it worked fine
    2. transaction dumps are still getting failed when the cron job is invoked here


    please provide me some detailed informaton what can be done to fix it permanently

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Did you change the DB option 'select into/bulkcopy/pllsort' to false?

  10. #10
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1
    its not set to false..you can check the attachements in the previous reply from me

  11. #11
    Join Date
    Feb 2014
    Posts
    42
    Provided Answers: 1
    you can see the db options in the previos reply from me

    i havent changed any options on that

  12. #12
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi again,

    Put the option "trunc log on chkpt" to false.
    sp_dboption [dbname], "trunc log on chkpt", false.

    Thats is why your cron job that tries to dump transactions is failling.

    How can you dump transactions to disk if you commit them and they are removed from the transaction log...

    So steps:

    1) sp_dboption [dbname], "trunc log on chkpt", false.

    2) Full dump of the Databases.
    Last edited by Catarrunas; 02-25-14 at 11:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •