Microsoft KB Archive/107710: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 97: | Line 97: | ||
) | ) | ||
/* */ | /* */ | ||
/* Check all the databases that are dumped daily, weekly, & | /* Check all the databases that are dumped daily, weekly, & biweekly */ | ||
/* Note: The dump can only occur if the start time(HH:MM) is | /* Note: The dump can only occur if the start time(HH:MM) is > the | ||
last dump <= now */ | last dump <= now */ | ||
/* */ | /* */ | ||
Line 111: | Line 111: | ||
and Frequency <= 14 | and Frequency <= 14 | ||
/* Freq daily, weekly, or biweekly */ | /* Freq daily, weekly, or biweekly */ | ||
and datediff(day, Last_dump, @now) | and datediff(day, Last_dump, @now) >= | ||
convert(smallint,Frequency) /* Freq time has elapsed */ | convert(smallint,Frequency) /* Freq time has elapsed */ | ||
and @now | and @now >= convert(datetime, @monthyear + Start_time) | ||
and datediff(hour, Last_dump, @now) | and datediff(hour, Last_dump, @now) >= | ||
convert(smallint,Frequency)*24 /* Freq time has elapsed */ | convert(smallint,Frequency)*24 /* Freq time has elapsed */ | ||
and datediff(minute, Last_dump, @now) | and datediff(minute, Last_dump, @now) >= | ||
convert(smallint,Frequency)*24*60 /* Freq time has elapsed */ | convert(smallint,Frequency)*24*60 /* Freq time has elapsed */ | ||
/* */ | /* */ | ||
Line 122: | Line 122: | ||
/* NOTE: First we get this week number, then do the same criteria as the | /* 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 | Daily, weekly, bi-weekly dump. The dump can only occur if the start | ||
time(HH:MM) is | time(HH:MM) is > the last dump <= now */ | ||
/* */ | /* */ | ||
declare @rundate datetime, @weekno smallint /* Get this week number */ | declare @rundate datetime, @weekno smallint /* Get this week number */ | ||
Line 139: | Line 139: | ||
and (convert(smallint,Day) = @dayofweek) | and (convert(smallint,Day) = @dayofweek) | ||
/* Dump today */ | /* Dump today */ | ||
and Frequency | and Frequency >= 31 /* Freq monthly */ | ||
and (convert(smallint,Frequency) - 30) = @weekno /* Week of month */ | and (convert(smallint,Frequency) - 30) = @weekno /* Week of month */ | ||
and datediff(day, Last_dump, @now) | and datediff(day, Last_dump, @now) >= 28 /* Freq time has elapsed */ | ||
and @now | and @now >= convert(datetime, @monthyear + Start_time) | ||
and datediff(hour, Last_dump, @now) | and datediff(hour, Last_dump, @now) >= convert(smallint,28)*24 | ||
/* Freq time has elapsed */ | /* Freq time has elapsed */ | ||
and datediff(minute, Last_dump, @now) | and datediff(minute, Last_dump, @now) >= | ||
convert(smallint,28)*24*60 /* Freq time has elapsed */ | convert(smallint,28)*24*60 /* Freq time has elapsed */ | ||
update #spdumptab set datacntrltype = (select cntrltype from | update #spdumptab set datacntrltype = (select cntrltype from |
Latest revision as of 12:25, 21 July 2020
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