Microsoft KB Archive/114523

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.2

-

BUG# 9613 (4.2)

SYMPTOMS
When a backup event is scheduled to occur monthly, the backup event may not occur every alternate month. For example, assume a monthly backup event scheduled on Oct. 26, 93 to occur on Tuesdays of the fourth week of every month (Tuesday monthly-week4). The next backup for that event will not occur on Nov. 23, 93. Instead, it will occur on Dec. 28, 93.

CAUSE
The stored procedure sp_MSbackup_now incorrectly calculates the time elapsed for the monthly backup event.

WORKAROUND
You can 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 OS/2:

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 a 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 */ )

/* */ /* Check all the databases that are dumped daily, weekly, and 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 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

DDaily, 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 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 update #spdumptab set logcntrltype = (select cntrltype from master..sysdevices s where set nocount off
 * 1) spdumptab.ddump = s.name)
 * 1) spdumptab.ldump = s.name)

/* */ /* Output the values to the daemon */ /* */

select id = id, name = name, owner = owner, ddump = ddump, ldump = ldump,dcntrl = datacntrltype, lcntrl = logcntrltype from #spdumptab

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

Additional query words: SQL Monitor Month

Keywords : kbtool kbusage SSrvAdmin SSrvStProc kbbug4.20 kbfix4.21

Version : 4.2

Platform : OS/2

Issue type :
 * }