on 02-23-2018 10:36 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ..?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"
else
print "tran log is not dumpable"
go
Cheers,
-bret
Hi,
There is a couple of scenarios when you get "Use DUMP DATABASE instead" message :
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.