Article ID: 107710
Article Last Modified on 3/26/2000
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
This article was previously published under Q107710
BUG# NT: 9613 (4.20)
SYMPTOMS
When you schedule a backup event to occur monthly, the backup event may not occur every alternate month. For example, assume a monthly backup event is scheduled on October 26, 1993, to occur on Tuesday of the fourth week every month (Tuesday monthly-week4). The next backup for that event will not occur on November 23, 1993; instead, it will occur on December 28, 1993.
CAUSE
The stored procedure sp_MSbackup_now incorrectly calculates the time elapsed for the monthly backup event.
WORKAROUND
Drop and recreate the stored procedure sp_MSbackup_now by running the following script using any query tool, such as ISQL or ISQL/w.
On SQL Server for Windows NT:
use master
go
drop proc sp_MSbackup_now
go
create procedure sp_MSbackup_now as
declare @now datetime, @dayofweek smallint, @hour smallint, @minute
smallint, @elapsed int, @monthyear varchar(30)
set nocount on
/* */
/* Get the current date and time. */
/* Parse out the relevant parts of the date */
/* */
select @now = getdate()
select @dayofweek = datepart(weekday,@now)
select @monthyear = substring(convert(varchar(12),getdate()),1,12)
/* */
/* Create temporary table that holds data on what needs to be dumped */
/* */
create table #spdumptab
(
id int Not Null, /* Unique identifier */
name varchar(30) Not Null, /* Name of database to be dumped */
owner varchar(30) Not Null, /* Name of the database owner */
ddump varchar(30) Null, /* Database dump device */
ldump varchar(30) Null, /* Log dump device */
datacntrltype smallint Null, /* Control type */
logcntrltype smallint Null, /* Control type */
status tinyint Null /* extra dump parameters */
)
/* */
/* Check all the databases that are dumped daily, weekly, & biweekly */
/* Note: The dump can only occur if the start time(HH:MM) is > the
last dump <= now */
/* */
insert into #spdumptab
select Event_id, Database_name,
Database_owner,Database_dump,Log_dump,NULL,NULL,Stat
from MSscheduled_backups
where Enabled = 1
/* Dump turned on */
and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)
/* Dump to day or Daily */
and Frequency <= 14
/* Freq daily, weekly, or biweekly */
and datediff(day, Last_dump, @now) >=
convert(smallint,Frequency) /* Freq time has elapsed */
and @now >= convert(datetime, @monthyear + Start_time)
and datediff(hour, Last_dump, @now) >=
convert(smallint,Frequency)*24 /* Freq time has elapsed */
and datediff(minute, Last_dump, @now) >=
convert(smallint,Frequency)*24*60 /* Freq time has elapsed */
/* */
/* Check all the databases that are dumped monthly */
/* NOTE: First we get this week number, then do the same criteria as the
Daily, weekly, bi-weekly dump. The dump can only occur if the start
time(HH:MM) is > the last dump <= now */
/* */
declare @rundate datetime, @weekno smallint /* Get this week number */
select @rundate = @now
select @weekno = 1
while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now)
begin
select @weekno = @weekno + 1
select @rundate = dateadd(day,-7,@rundate)
end
insert into #spdumptab
select Event_id, Database_name,
Database_owner,Database_dump,Log_dump,NULL,NULL,Stat
from MSscheduled_backups
where Enabled = 1 /* Dump turned on */
and (convert(smallint,Day) = @dayofweek)
/* Dump today */
and Frequency >= 31 /* Freq monthly */
and (convert(smallint,Frequency) - 30) = @weekno /* Week of month */
and datediff(day, Last_dump, @now) >= 28 /* Freq time has elapsed */
and @now >= convert(datetime, @monthyear + Start_time)
and datediff(hour, Last_dump, @now) >= convert(smallint,28)*24
/* Freq time has elapsed */
and datediff(minute, Last_dump, @now) >=
convert(smallint,28)*24*60 /* Freq time has elapsed */
update #spdumptab set datacntrltype = (select cntrltype from
master..sysdevices s where
#spdumptab.ddump = s.name)
update #spdumptab set logcntrltype = (select cntrltype from
master..sysdevices s where
#spdumptab.ldump = s.name)
set nocount off
/* */
/* Output the values to the daemon */
/* */
select id = id, name = name, owner = owner,
ddump = ddump, ldump = ldump,dcntrl = datacntrltype,
lcntrl = logcntrltype, stat = status from #spdumptab
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2 for Windows NT. This problem was corrected in SQL Server version 4.21. For more information, contact your primary support provider.
Additional query words: SQL Monitor Month Windows NT
Keywords: kbbug kbfix kbenv KB107710
