Create backup image

[bjanker77]

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

[VolkerBarth]

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

[bjanker77]

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

[VolkerBarth]

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.

[chris_keating]

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.

[bjanker77]

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.