Hi.

Our most used backup schedules is using the maintenance plans with a schedule each morning, afternoon and night. We are creating a set of 3 backup images in 3 separate folders. However, our biggest customer insists on having backups 4 times each day. At 01, 12, 18 and 21. The backups at 01, 18 and 21 usually finishes in 7-10 minutes (45 GB database). But the one at 12 o'clock tends to take very long time, due to high activity in the database in the middle of the day. Yesterday it used over 5 hours and last saturday (which is normally a quiet day) it was still running at 18. But it finished as soon as I disconnected a user which obviously was causing a lock.

Is there any way to discover which connections that are preventing the backup from getting done? Or perhaps even get some sort of notification from SQL Anywhere that the backup is hanging?

Or perhaps we're using a wrong approach to the whole backup routine?

Any suggestions is very welcome. :)

regards,

Bjarne

asked 21 Mar, 03:58

Bjarne%20Anker's gravatar image

Bjarne Anker
520212236
accept rate: 0%

edited 21 Mar, 07:35

Volker%20Barth's gravatar image

Volker Barth
33.9k333487718

So are these all full backups (i.e. database file(s) and transaction log are copied)?

I guess it's way more common to do a full backup, say, once a day (probably at a time with low usage like at night), and then incremental backups (i.e. just the transaction log) quite often (say, every quarter/half an hour or the like).

Backups are influenced by current transactions, see the WAIT BEFORE/AFTER clauses. If the full backup is blocked by current transactions, I suspect that these options are not optimally chosen - however, the backup options do also influence whether you can validate the backup copy itself and how to do a restore, so that should fit together.

(21 Mar, 04:19) Volker Barth
Replies hidden

FWIW, you have chosen the tag "live-backup" - do you really relate to the "live backup" facility, i.e. running DBBACKUP -l ...?

(21 Mar, 05:31) Volker Barth

Hi.

I guess the "live-backup" tag was not quite right in this scenario. We're talking a full backup with db and log files yes.

I will certainly look into the incremental backups, at least the one in the middle of the day.

Thanks,

Bjarne

(21 Mar, 05:59) Bjarne Anker
Replies hidden

if they want real-real copy of the DB, would it make sense and use "Database mirroring"? Then they will get a 1-to-1 copy of the DB in real time.

(21 Mar, 07:07) Vlad

You probably mean "online backup" which is a backup taken while the database is running. The term "live backup" is actually a thing: a continuous backup taken for high availability purposes.

Online backups don't care about ordinary locks, but they do care a lot about the WAIT AFTER END clause: "Use this clause with caution as new, incoming transactions can cause the backup to wait indefinitely."

Personally, I have never been able to use WAIT BEFORE START or WAIT AFTER END successfully in production... works fine in test, though :)

(21 Mar, 07:27) Breck Carter
Replies hidden

FWIW it is possible to write SQL code to drop all other connections. This code could be enhanced to only drop other connections that have running transactions... or to send emails to Security about the offenders :)

(21 Mar, 07:33) Breck Carter

Personally, I have never been able to use WAIT BEFORE START or WAIT AFTER END successfully in production.

Yes, been there, too. Unfortunately, the maintenance plans as created within SQL Central do use the WAIT BEFORE START clause by default, so that may affect Bjarne, too. - I had to drop that clause when using maintenance plans.

(21 Mar, 07:33) Volker Barth

OK, then I'll remove that tag from your question...:)

(21 Mar, 07:34) Volker Barth
showing 5 of 8 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×74

question asked: 21 Mar, 03:58

question was seen: 161 times

last updated: 21 Mar, 07:35