FIX: SQL Admin: Actual Startup Time for Scheduled Backups

ID: Q87834

The information in this article applies to:

Microsoft SQL Server version 4.2x
Microsoft SQL Server for OS/2, version 4.2

BUG# 7256 (4.20)

SYMPTOMS

When daily backup events are scheduled using SQL Administrator, the actual start time for the backup event shifts every day by approximately the amount of time taken for the backup and the SQL Monitor "backuptime" parameter value. For example, if two daily backup events are scheduled to start at 6:00 P.M. and 6:40 P.M. and the SQL Monitor "backuptime" parameter is set to fifteen minutes, if SQL Monitor wakes up at 5:47 P.M. and every fifteen minutes thereafter, the actual startup times may shift successively each day, according to the following chart:


   Event ID   Actual Start Time         End Time
   --------   ----------------------    ----------------------
   1          April 17 1992 6:02 P.M.   April 17 1992 6:02 P.M.
   2          April 17 1992 6:47 P.M.   April 17 1992 6:47 P.M.
   1          April 18 1992 6:17 P.M.   April 17 1992 6:17 P.M.
   2          April 18 1992 7:02 P.M.   April 17 1992 7:02 P.M.
   1          April 19 1992 6:32 P.M.   April 17 1992 6:32 P.M.
   2          April 19 1992 7:17 P.M.   April 17 1992 7:17 P.M.

This case assumes that the actual backup time is less than a minute.

CAUSE

This problem occurs because of calculation drift. When SQL Monitor wakes up and finds that less than 24 hours have passed since the last backup was finished, it does not do the backup at that time. Accordingly, the backup is done when it wakes up next time. This happens every time SQL Monitor is supposed to perform the backup; hence, the successive backups are shifted each day.

WORKAROUND

To specify the exact or near-exact time to start a backup, you must periodically update the Last_dump column of the table MSScheduled_backups to match the date of the last dump but at the specified start time. For example, if the last dump is on 4/18/92 at 6:17 P.M. and your start time is at 6:00 P.M. for the event_id 1, execute the following:


   Update MSScheduled_backups
   set last_dump='4/18/92 6:00PM'
   where event_id= 1

Therefore, on 4/19/92, the backup for event_id 1, the backup will occur at 6:02 P.M.

Another way to reduce the drift is to set the SQL Monitor "backuptime" parameter to a lower value, say from fifteen minutes to one or two minutes. This reduces the drift over time.

STATUS

Microsoft has confirmed this to be a problem in SQL Administrator version 4.2. This problem was corrected in SQL Administrator version 4.20.1 and SQL Server 4.2a for OS/2. For more information, contact your primary support provider.

Additional query words: SQL Admin

Keywords : kbtool SSrvAdmin kbbug4.20 kbbug4.20a
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS
Issue type :