hi
I have a backup up strategy as follows
Differential - every 4 hrs(4am,8am,12pm,...)
transaction - every 10 min
I am using SQL SERVER 2000.
At Some point of time my Differential and Transaction backup clashes
at(4am,8am,...)
when i Check my Entriprise Manager(Locks/process id)
I find Spid (blocked).
1)spid = 661
Properties Window
BACKUP DATABASE [imcl] TO [DiffBkp_IMCL] WITH NOINIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'IMCL Device Differential Backup', NOSKIP ,
STATS = 10, NOFORMAT
2)spid 708 blocked by 661
Properties Window
BACKUP LOG [imcl] TO DISK =
N'F:\DatabaseBackups\IMCL\Tran\NewTranImcl\imcl\im cl_tlog_200505261200.TRN'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
What I want to know is - :
a)Should I ignore this Blocking as it is solved automatically.
b)Whether my backup plan is poor.
c)Will this effect my users who are connected to my Server.
Thank u in advanceThe blocking will be resolved, in that spid 708 is essentially 'on
hold' until 661 completes. Probably the easiest way to avoid the issue
is to set the log backups to run every ten minutes but starting at five
minutes past the hour instead of on the hour, which I guess is what you
currently have.
If the differential backup takes more than 5 minutes, then you'll still
get the blocking, though, so if this is the case, then you might want
to create multiple active schedules for your transaction log backups -
the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
whatever interval allows the differential backup to complete without
blocking) etc.
Your backup plan is fine in principle - see "Reducing Recovery Time" in
Books Online (provided that you're also making full backups at some
point, of course).
Simon|||Thank you Simon for advice
Simon Hayes wrote:
> The blocking will be resolved, in that spid 708 is essentially 'on
> hold' until 661 completes. Probably the easiest way to avoid the issue
> is to set the log backups to run every ten minutes but starting at five
> minutes past the hour instead of on the hour, which I guess is what you
> currently have.
> If the differential backup takes more than 5 minutes, then you'll still
> get the blocking, though, so if this is the case, then you might want
> to create multiple active schedules for your transaction log backups -
> the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
> whatever interval allows the differential backup to complete without
> blocking) etc.
> Your backup plan is fine in principle - see "Reducing Recovery Time" in
> Books Online (provided that you're also making full backups at some
> point, of course).
> Simon
