Hi.

We have a customer with a rather large database (55GB). We have setup backup 4 times a day, at 1AM, 11AM, 6PM and 9PM (01, 11, 18 and 21 CET) using maintenance plans. Full backup each time.

The maintenance plans for 1AM, 6PM and 9PM runs just fine always.

But the plan for 11AM sometimes never starts. It just says "0% completed" and never finishes. Last week it happended 4 times and we had to cancel it after 5 hours (Still "0% completed).

There is a lot of transactions in the database and a lot of connections, but if we create a backup image manually it always works just fine. Even at high load/usage.

Is there any way to check why the maintenance plan doesn't start/complete?

br,

Bjarne Anker Maritech Systems Norway

asked 12 Oct '20, 04:43

Bjarne%20Anker's gravatar image

Bjarne Anker
745323648
accept rate: 10%

Do you use any of the WAIT BEFORTE / WAIT AFTER clauses?

(12 Oct '20, 05:22) Volker Barth

Hi.

There is no WAIT BEFORE or WAIT AFTER as far as I can see. Perhaps that's a good solution for the midday backup when there is a lot of transaction? Or will that only make things worse since there always will be a lot of connections open?

Bjarne

(12 Oct '20, 05:29) Bjarne Anker
Replies hidden
1

Well, IIRC, the maintenance plans (MPs) do use one of these clauses by default ( guess "WAIT BEFORE START"), and we ran into similar problems (i.e. "blocked backups"), so we adapted the underlying events used by the MPs manually to exclude those clauses.

AFAIK, the MPs report their success both in the console log and in table dbo.maint_plan_report.

(12 Oct '20, 11:22) Volker Barth
3

The backup plan is implemented as an event with the name <maintenance_plan_name>_event. There is a WAIT BEFORE START; here is the statement that is generated:

BACKUP DATABASE DIRECTORY 'dir_name' WAIT BEFORE START;

As Volker suggests you can adjust the event logic to remove the WAIT BEFORE START. Please note that if you manually change the event and later modify the maintenance plan, it will recreate the event with the WAIT BEFORE START.

(14 Oct '20, 08:15) Chris Keating

Actually, we have a WAIT BEFORE START in the maintenance plan (didn't notice it until now). We will comment it out of the event and check the job the next couple of days.

Thanks.

(14 Oct '20, 08:49) Bjarne Anker
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:

×260
×84

question asked: 12 Oct '20, 04:43

question was seen: 787 times

last updated: 14 Oct '20, 08:58